Include the database definition language (DDL) scripts to CREATE to database sch

Question

Include the database definition language (DDL) scripts to CREATE to database schema as described in the entity–relationship (E–R) diagram (Retail Store).

Include the database manipulation scripts (DML) that will be used to INSERT, DELETE, and UPDATE data in the proposed database system.

Include the SELECT, CROSSTAB, and AGGREGATE FUNCTION statements that will be used to read data from the proposed database system.

Provide your analysis as to how this part of the project fulfills the mission and 1 or more goals of the case study organization.

Provide the following attachments (in addition to embedding in document):

DDL.sql (including CREATE and INSERT statements so that they execute in the correct order [top-down])

DML.sql (including DELETE and UPDATE statements so that they can be executed in any order as selected)

REPORT.sql (including SELECT, CROSSTAB, AGGREGATE FUNCTION statements so that they can be executed in any order as selected)

CUSTOMERS custID fNAME INAME E MP LOYEE em ployeelD SERVICES servicelD Frequency ITEMS ORDERS producti D orderlD SURVEYS surveylD empS un cust Sur Product# Transaction# Store#

Explanation / Answer

drop table [Customers]
drop table Employees
drop table Order_items
drop table Products
=========================================
CREATE TABLE [dbo].[Customers](
[customers_id] [bigint] NOT NULL,
[customer_name] [varchar](20) NOT NULL,
[customer_phone] [varchar](15) NOT NULL,
[customer_email] [varchar](30) NOT NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[customers_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

=================================================

CREATE TABLE [dbo].[Employees](
[employe_id] [bigint] NOT NULL,
[employe_name] [varchar](20) NOT NULL,
[employe_position] [varchar](20) NOT NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[employe_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

==========================================

CREATE TABLE [dbo].[Order_Items](
[order_id] [bigint] NOT NULL,
[product_id] [bigint] NOT NULL,
[quantity] [bigint] NULL,
CONSTRAINT [PK_Order_Items] PRIMARY KEY CLUSTERED
(
[order_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

=============================================

CREATE TABLE [dbo].[Orders](
[order_id] [bigint] NOT NULL,
[customer_id] [bigint] NOT NULL,
[order_status] [varchar](50) NULL,
[order_date] [date] NULL,
[order_details] [text] NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[order_id] 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]

========================================
CREATE TABLE [dbo].[Products](
[products_id] [bigint] NOT NULL,
[product_name] [varchar](50) NOT NULL,
[product_price] [numeric](18, 0) NOT NULL,
[product_description] [text] NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[products_id] 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]

==================================================

DELETE FROM [dbo].[Order_Items]

DELETE FROM [dbo].[Products]

DELETE FROM [dbo].[Employees]

DELETE FROM [dbo].[Customers]

INSERT INTO [dbo].[Customers] ([customers_id], [customer_name], [customer_phone], [customer_email])
VALUES (1, 'PQR', '09113321', 'emailid')

INSERT INTO [dbo].[Customers] ([customers_id], [customer_name], [customer_phone], [customer_email])
VALUES ( 2, 'ABC', '1213123424', 'emailid')

INSERT INTO [dbo].[Customers] ([customers_id], [customer_name], [customer_phone], [customer_email])
VALUES (3, 'LSK', '13131234235', 'emailid')

=================================================================================================
INSERT INTO [dbo].[Employees] ([employe_id], [employe_name], [employe_position])
VALUES (1, 'PQR', '1')

INSERT INTO [dbo].[Employees] ([employe_id], [employe_name], [employe_position])
VALUES (2, 'XYZ', '2')

INSERT INTO [dbo].[Employees] ([employe_id], [employe_name], [employe_position])
VALUES (3, 'ABC','3')

=================================================================================================
INSERT INTO [dbo].[Order_Items] ([order_id], [product_id], [quantity])
VALUES ( 1, 100000, 1)

INSERT INTO [dbo].[Order_Items] ([order_id], [product_id], [quantity])
VALUES (2, 100000, 1)

INSERT INTO [dbo].[Order_Items] ([order_id], [product_id], [quantity])
VALUES (3, 100000, 1)

=========================================================

INSERT INTO [dbo].[Orders] ([order_id], [customer_id], [order_status], [order_date], [order_details])
VALUES (1, 1, 'Normal', '20140726', 'Order details 1')

INSERT INTO [dbo].[Orders] ([order_id], [customer_id], [order_status], [order_date], [order_details])
VALUES (2, 2, 'Normal', '20140726', 'Order details 2')

INSERT INTO [dbo].[Orders] ([order_id], [customer_id], [order_status], [order_date], [order_details])
VALUES (3, 3, 'Normal', '20140726', 'Order details 3')

=============================================================================
INSERT INTO [dbo].[Products] ([products_id],[product_name], [product_price],[product_description])
VALUES ( 14, 'test1', 100000, 'Product descrtion 14')

INSERT INTO [dbo].[Products] ([products_id], [product_name], [product_price], [product_description] )
VALUES ( 15, 'test2', 5000, 'Product descrtion 15')

INSERT INTO [dbo].[Products] ([products_id], [product_name], [product_price], [product_description])
VALUES (16, 'test3', 50000 ,'product descrtion 16')
=================================================

DELETE FROM [dbo].[Order_Items] WHERE <Search Conditions,,>
GO

DELETE FROM [dbo].[Products] WHERE <Search Conditions,,>
GO

DELETE FROM [dbo].[Employees] WHERE <Search Conditions,,>
GO

DELETE FROM [dbo].[Customers] WHERE <Search Conditions,,>
GO

=========================================================

UPDATE [dbo].[Products]
SET [products_id] = <products_id, bigint,>
,[product_name] = <product_name, varchar(50),>
,[product_price] = <product_price, numeric(18,0),>
,[product_description] = <product_description, text,>
WHERE <Search Conditions,,>
Go

UPDATE [dbo].[Order_Items]
SET [order_id] = <order_id, bigint,>
,[product_id] = <product_id, bigint,>
,[quantity] = <quantity, bigint,>
WHERE <Search Conditions,,>
GO

UPDATE [dbo].[Employees]
SET [employe_id] = <employe_id, bigint,>
,[employe_name] = <employe_name, varchar(20),>
,[employe_position] = <employe_position, varchar(20),>
WHERE <Search Conditions,,>
GO

UPDATE [dbo].[Customers]
SET [customers_id] = <customers_id, bigint,>
,[customer_name] = <customer_name, varchar(20),>
,[customer_phone] = <customer_phone, varchar(15),>
,[customer_email] = <customer_email, varchar(30),>
WHERE <Search Conditions,,>
GO

=======================================================
select b.customer_name, sum(quantity*product_price)
from
(
select a.products_id,a.product_price,b.quantity
from Products a,Order_Items b
where a.products_id = b.product_id

)a,Customers b
group by customer_name

===========================================================
select customer_id,b.customer_name, sum(quantity*product_price)
from
(
select a.products_id,a.product_price,b.order_id,b.quantity,c.customer_id
from Products a,Order_Items b ,Orders c
where a.products_id = b.product_id
and b.order_id = c.order_id
and c.order_date between '2014-07-01' and '2014-07-31'
)a,Customers b
where a.customer_id = b.customers_id
group by customer_id,customer_name

===================================================
select customer_id,b.customer_name, sum(quantity*product_price)
from
(
select a.products_id,a.product_price,b.order_id,b.quantity,c.customer_id
from Products a,Order_Items b ,Orders c
where a.products_id = b.product_id
and b.order_id = c.order_id
and c.order_date between '2014-07-01' and '2014-07-31'
)a,Customers b
where a.customer_id = b.customers_id
group by customer_id,customer_name
having sum(quantity*product_price) > 10000

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---