• Author / Uploaded
• RafaelVillajuan

Citation preview

TRABAJO DE BASE DE DATOS DE RESTAURANTE Para este trabajo de investigación se realizo una base de datos de un restaurante Adjunto el diagrama generado y script

Script USE [master] GO /****** Object: Database [RESTAURANTE] Script Date: 04/08/2019 7:46:01 ******/ CREATE DATABASE [RESTAURANTE] CONTAINMENT = NONE ON PRIMARY ( NAME = N'RESTAURANTE', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\RESTAURANTE.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'RESTAURANTE_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\RESTAURANTE_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO ALTER DATABASE [RESTAURANTE] SET COMPATIBILITY_LEVEL = 120 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [RESTAURANTE].[dbo].[sp_fulltext_database] @action = 'enable' end GO ALTER DATABASE [RESTAURANTE] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [RESTAURANTE] SET ANSI_NULLS OFF GO ALTER DATABASE [RESTAURANTE] SET ANSI_PADDING OFF

Ing. Sistemas III Ciclo 2019-I ALUMNO: RAFAEL VILLAJUAN HUAYNATES

GO ALTER DATABASE [RESTAURANTE] SET ANSI_WARNINGS OFF GO ALTER DATABASE [RESTAURANTE] SET ARITHABORT OFF GO ALTER DATABASE [RESTAURANTE] SET AUTO_CLOSE OFF GO ALTER DATABASE [RESTAURANTE] SET AUTO_SHRINK OFF GO ALTER DATABASE [RESTAURANTE] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [RESTAURANTE] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [RESTAURANTE] SET CURSOR_DEFAULT GLOBAL GO ALTER DATABASE [RESTAURANTE] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [RESTAURANTE] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [RESTAURANTE] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [RESTAURANTE] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [RESTAURANTE] SET DISABLE_BROKER GO ALTER DATABASE [RESTAURANTE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [RESTAURANTE] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [RESTAURANTE] SET TRUSTWORTHY OFF GO ALTER DATABASE [RESTAURANTE] SET ALLOW_SNAPSHOT_ISOLATION OFF GO ALTER DATABASE [RESTAURANTE] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [RESTAURANTE] SET READ_COMMITTED_SNAPSHOT OFF GO ALTER DATABASE [RESTAURANTE] SET HONOR_BROKER_PRIORITY OFF GO ALTER DATABASE [RESTAURANTE] SET RECOVERY FULL GO ALTER DATABASE [RESTAURANTE] SET MULTI_USER GO ALTER DATABASE [RESTAURANTE] SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE [RESTAURANTE] SET DB_CHAINING OFF GO ALTER DATABASE [RESTAURANTE] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) GO ALTER DATABASE [RESTAURANTE] SET TARGET_RECOVERY_TIME = 0 SECONDS GO ALTER DATABASE [RESTAURANTE] SET DELAYED_DURABILITY = DISABLED GO EXEC sys.sp_db_vardecimal_storage_format N'RESTAURANTE', N'ON' GO USE [RESTAURANTE] GO /****** Object: Table [dbo].[CARTA] Script Date: 04/08/2019 7:46:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

Ing. Sistemas III Ciclo 2019-I ALUMNO: RAFAEL VILLAJUAN HUAYNATES

CREATE TABLE [dbo].[CARTA]( [ID_CARTA] [nchar](10) NOT NULL, [ID_RESTAURANTE] [nchar](10) NULL, [ID_PLATO] [nchar](10) NULL, [DESCRIPCION] [nchar](10) NULL, CONSTRAINT [PK_CARTA] PRIMARY KEY CLUSTERED ( [ID_CARTA] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[CATEGORIA] Script Date: 04/08/2019 7:46:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[CATEGORIA]( [ID_CATEGORIA] [nchar](10) NOT NULL, [ID_ENCARGADO] [nchar](10) NULL, [DESCRIPCION] [nchar](20) NULL, CONSTRAINT [PK_CATEGORIA] PRIMARY KEY CLUSTERED ( [ID_CATEGORIA] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DETALLE DE PEDIDO] Script Date: 04/08/2019 7:46:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DETALLE DE PEDIDO]( [ID_DETALLE DE PEDIDO ID_DETALLEDEPEDIDO] [nchar](10) NOT NULL, [ID_PLATO] [nchar](20) NULL, [SUBTOTAL] [decimal](18, 0) NULL, [CANTIDAD] [int] NULL, CONSTRAINT [PK_DETALLE DE PEDIDO] PRIMARY KEY CLUSTERED ( [ID_DETALLE DE PEDIDO ID_DETALLEDEPEDIDO] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[ENCARGADO] Script Date: 04/08/2019 7:46:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ENCARGADO]( [ID_ENCARGADO] [nchar](10) NOT NULL, [NOMBRE] [ntext] NULL, [APELLIDOS] [ntext] NULL,

Ing. Sistemas III Ciclo 2019-I ALUMNO: RAFAEL VILLAJUAN HUAYNATES

[EDAD] [int] NULL, [DNI] [numeric](18, 0) NULL, [DIRECCION] [nchar](10) NULL, CONSTRAINT [PK_ENCARGADO] PRIMARY KEY CLUSTERED ( [ID_ENCARGADO] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[INGREDIENTE] Script Date: 04/08/2019 7:46:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[INGREDIENTE]( [ID_INGREDIENTE] [nchar](10) NOT NULL, [NOMBRE] [nchar](30) NULL, [PRECIO COMPRA] [decimal](18, 0) NULL, [STOCK ALMACEN] [int] NULL, CONSTRAINT [PK_INGREDIENTE] PRIMARY KEY CLUSTERED ( [ID_INGREDIENTE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[MEDIDA] Script Date: 04/08/2019 7:46:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[MEDIDA]( [ID_MEDIDA] [nchar](10) NOT NULL, [DESCRIPCION] [nchar](20) NULL, CONSTRAINT [PK_MEDIDA] PRIMARY KEY CLUSTERED ( [ID_MEDIDA] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[NIVEL DE DIFICULTAD] Script Date: 04/08/2019 7:46:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[NIVEL DE DIFICULTAD]( [ID_DIFICULTAD] [nchar](10) NOT NULL, [DESCRIPCION] [nchar](15) NULL, CONSTRAINT [PK_NIVEL DE DIFICULTAD] PRIMARY KEY CLUSTERED ( [ID_DIFICULTAD] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

Ing. Sistemas III Ciclo 2019-I ALUMNO: RAFAEL VILLAJUAN HUAYNATES

GO /****** Object: Table [dbo].[PEDIDO DE VENTA] Script Date: 04/08/2019 7:46:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[PEDIDO DE VENTA]( [ID_PEDIDO] [nchar](10) NOT NULL, [ID_DETALLEPEDIDO] [nchar](10) NULL, [FECHA] [date] NULL, [MONTOFINAL] [decimal](18, 0) NULL, CONSTRAINT [PK_PEDIDO DE VENTA] PRIMARY KEY CLUSTERED ( [ID_PEDIDO] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[PLATO] Script Date: 04/08/2019 7:46:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[PLATO]( [ID_PLATO] [nchar](10) NOT NULL, [ID_NDEDIDIFICULTAD] [nchar](10) NULL, [ID_CATEGORIA] [nchar](10) NULL, [ID_RECETA] [nchar](10) NULL, [NOMBRE] [nchar](30) NULL, [DESCRIPCION] [nchar](50) NULL, [PRECIO] [decimal](18, 0) NULL, CONSTRAINT [PK_PLATO] PRIMARY KEY CLUSTERED ( [ID_PLATO] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[RECETA] Script Date: 04/08/2019 7:46:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[RECETA]( [ID_RECETA] [nchar](10) NOT NULL, [ID_INGREDIENTE] [nchar](10) NULL, [ID_MEDIDA] [nchar](10) NULL, [DESCRIPCION] [nchar](50) NULL, [CANTIDAD] [int] NULL, CONSTRAINT [PK_RECETA] PRIMARY KEY CLUSTERED ( [ID_RECETA] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: ******/

Table [dbo].[RESTAURANTE]

Script Date: 04/08/2019 7:46:02

Ing. Sistemas III Ciclo 2019-I ALUMNO: RAFAEL VILLAJUAN HUAYNATES

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[RESTAURANTE]( [ID_RESTAURANTE] [nchar](10) NOT NULL, [ID_CARTA] [nchar](10) NULL, [ID_PEDIDO] [nchar](10) NULL, [NOMBRE] [numeric](18, 0) NULL, [UBICACION] [nchar](50) NULL, CONSTRAINT [PK_RESTAURANTE] PRIMARY KEY CLUSTERED ( [ID_RESTAURANTE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CARTA] WITH CHECK ADD CONSTRAINT [FK_CARTA_PLATO] FOREIGN KEY([ID_PLATO]) REFERENCES [dbo].[PLATO] ([ID_PLATO]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[CARTA] CHECK CONSTRAINT [FK_CARTA_PLATO] GO ALTER TABLE [dbo].[CARTA] WITH CHECK ADD CONSTRAINT [FK_CARTA_RESTAURANTE] FOREIGN KEY([ID_RESTAURANTE]) REFERENCES [dbo].[RESTAURANTE] ([ID_RESTAURANTE]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[CARTA] CHECK CONSTRAINT [FK_CARTA_RESTAURANTE] GO ALTER TABLE [dbo].[CATEGORIA] WITH CHECK ADD CONSTRAINT [FK_CATEGORIA_ENCARGADO] FOREIGN KEY([ID_ENCARGADO]) REFERENCES [dbo].[ENCARGADO] ([ID_ENCARGADO]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[CATEGORIA] CHECK CONSTRAINT [FK_CATEGORIA_ENCARGADO] GO ALTER TABLE [dbo].[PEDIDO DE VENTA] WITH CHECK ADD CONSTRAINT [FK_PEDIDO DE VENTA_DETALLE DE PEDIDO] FOREIGN KEY([ID_DETALLEPEDIDO]) REFERENCES [dbo].[DETALLE DE PEDIDO] ([ID_DETALLE DE PEDIDO ID_DETALLEDEPEDIDO]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[PEDIDO DE VENTA] CHECK CONSTRAINT [FK_PEDIDO DE VENTA_DETALLE DE PEDIDO] GO ALTER TABLE [dbo].[PLATO] WITH CHECK ADD CONSTRAINT [FK_PLATO_CATEGORIA] FOREIGN KEY([ID_CATEGORIA]) REFERENCES [dbo].[CATEGORIA] ([ID_CATEGORIA]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[PLATO] CHECK CONSTRAINT [FK_PLATO_CATEGORIA] GO ALTER TABLE [dbo].[PLATO] WITH CHECK ADD CONSTRAINT [FK_PLATO_NIVEL DE DIFICULTAD] FOREIGN KEY([ID_NDEDIDIFICULTAD])

Ing. Sistemas III Ciclo 2019-I ALUMNO: RAFAEL VILLAJUAN HUAYNATES

REFERENCES [dbo].[NIVEL DE DIFICULTAD] ([ID_DIFICULTAD]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[PLATO] CHECK CONSTRAINT [FK_PLATO_NIVEL DE DIFICULTAD] GO ALTER TABLE [dbo].[PLATO] WITH CHECK ADD CONSTRAINT [FK_PLATO_RECETA] FOREIGN KEY([ID_RECETA]) REFERENCES [dbo].[RECETA] ([ID_RECETA]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[PLATO] CHECK CONSTRAINT [FK_PLATO_RECETA] GO ALTER TABLE [dbo].[RECETA] WITH CHECK ADD CONSTRAINT [FK_RECETA_INGREDIENTE] FOREIGN KEY([ID_INGREDIENTE]) REFERENCES [dbo].[INGREDIENTE] ([ID_INGREDIENTE]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[RECETA] CHECK CONSTRAINT [FK_RECETA_INGREDIENTE] GO ALTER TABLE [dbo].[RECETA] WITH CHECK ADD CONSTRAINT [FK_RECETA_MEDIDA] FOREIGN KEY([ID_MEDIDA]) REFERENCES [dbo].[MEDIDA] ([ID_MEDIDA]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[RECETA] CHECK CONSTRAINT [FK_RECETA_MEDIDA] GO ALTER TABLE [dbo].[RESTAURANTE] WITH CHECK ADD CONSTRAINT [FK_RESTAURANTE_CARTA] FOREIGN KEY([ID_CARTA]) REFERENCES [dbo].[CARTA] ([ID_CARTA]) GO ALTER TABLE [dbo].[RESTAURANTE] CHECK CONSTRAINT [FK_RESTAURANTE_CARTA] GO ALTER TABLE [dbo].[RESTAURANTE] WITH CHECK ADD CONSTRAINT [FK_RESTAURANTE_PEDIDO DE VENTA] FOREIGN KEY([ID_PEDIDO]) REFERENCES [dbo].[PEDIDO DE VENTA] ([ID_PEDIDO]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[RESTAURANTE] CHECK CONSTRAINT [FK_RESTAURANTE_PEDIDO DE VENTA] GO USE [master] GO ALTER DATABASE [RESTAURANTE] SET READ_WRITE GO

Ing. Sistemas III Ciclo 2019-I ALUMNO: RAFAEL VILLAJUAN HUAYNATES