CSG1207/CSI5135 Systems and Database Design

Assignment 2(TASK 2) Database Design & Implementation
Student Name: Student ID:
1
Table of Contents Create.sql............................................................................................................................................2 Views.sql...........................................................................................................................................11 Queries.sql........................................................................................................................................11
List of figures
Figure 1: customer table.........................................................................................................................2 Figure 2: address table............................................................................................................................4 Figure3: Referral.....................................................................................................................................4 Figure 4: item_detail...............................................................................................................................5 Figure 5: item_category..........................................................................................................................6 Figure 6: category_newsletter................................................................................................................7 Figure 7: order........................................................................................................................................9 Figure 8: order_details..........................................................................................................................10 Figure 9: customer view........................................................................................................................11 Figure 10: order view............................................................................................................................11 Figure 11: ordered item view................................................................................................................11 Figure 12: query 1.................................................................................................................................11 Figure 13:query 2.................................................................................................................................12 Figure 14: query 3.................................................................................................................................12 Figure 15: query 4.................................................................................................................................12 Figure 16: query 5.................................................................................................................................13 Figure 17: query  6................................................................................................................................13 Figure 18: query 8.................................................................................................................................14
2
Create.sql
Customer table
CREATE TABLE [dbo].[customer](
[cust_number] [int] NOT NULL,
[first_name] [text] NOT NULL,
[email_address] [text]NOT NULL,
[last_name] [text] NOT NULL,
[newsletter] [text] NOT NULL,
CONSTRAINT [PK_customer] PRIMARY KEY CLUSTERED
(
[cust_number] 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
Figure1: customer table
3
Address table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[address](
[address_id] [int] NOT NULL,
[cust_number] [int] NOT NULL,
[address_details] [text] NOT NULL,
[address_name] [text] NULL,
CONSTRAINT [PK_address] PRIMARY KEY CLUSTERED
(
[address_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]
GO
ALTER TABLE [dbo].[address]  WITH CHECK ADD  CONSTRAINT [FK_address_customer] FOREIGN KEY([cust_number]) /******foreign key with customer table******/
REFERENCES [dbo].[customer] ([cust_number])
GO
ALTER TABLE [dbo].[address] CHECK CONSTRAINT [FK_address_customer]
GO
4
Figure2: address table Referral table
CREATE TABLE [dbo].[refferal](
[id] [int] NOT NULL,
[customer_number] [int] NOT NULL,
[reffered_number] [int] NOT NULL,
CONSTRAINT [PK_refferal] PRIMARY KEY CLUSTERED
(
[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]
GO
Figure3: Referral
5
Item_detailstable
CREATE TABLE [dbo].[item_detail](
[item_number] [int] NOT NULL,
[item_name] [text] NOT NULL,
[description] [text] NOT NULL,
[price] [int] NOT NULL,
CONSTRAINT [PK_item_detail] PRIMARY KEY CLUSTERED
(
[item_number] 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
Figure4: item_detail
6
Item_categorytable
CREATE TABLE [dbo].[item_category](
[category_number] [int] NOT NULL,
[category_name] [text] NOT NULL,
[item_number] [int] NOT NULL,
CONSTRAINT [PK_item_category] PRIMARY KEY CLUSTERED
(
[category_number] 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
ALTER TABLE [dbo].[item_category]  WITH CHECK ADD  CONSTRAINT [FK_item_category_item_detail] FOREIGN KEY([item_number])
REFERENCES [dbo].[item_detail] ([item_number]) /******foreign key with item_detail******/
GO
ALTER TABLE [dbo].[item_category] CHECK CONSTRAINT [FK_item_category_item_detail]
GO
Figure5: item_category
7
Category_newslettertable
CREATE TABLE [dbo].[category_newsletter](
[category_number] [int] NOT NULL,
[cust_number] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[category_newsletter]  WITH CHECK ADD  CONSTRAINT [FK_category_newsletter_customer] FOREIGN KEY([cust_number]) /******foreign key with customer table******/
REFERENCES [dbo].[customer] ([cust_number])
GO
ALTER TABLE [dbo].[category_newsletter] CHECK CONSTRAINT [FK_category_newsletter_customer]
GO
ALTER TABLE [dbo].[category_newsletter]  WITH CHECK ADD  CONSTRAINT [FK_category_newsletter_item_category] FOREIGN KEY([category_number]) /******foreign key with item_category******/
REFERENCES [dbo].[item_category] ([category_number])
GO
ALTER TABLE [dbo].[category_newsletter] CHECK CONSTRAINT [FK_category_newsletter_item_category]
GO
Figure6: category_newsletter
8
Order table
CREATE TABLE [dbo].[order](
[invoice_no] [int] NOT NULL,
[cust_number] [int] NOT NULL,
[date_order] [date] NOT NULL,
[billing_address_id] [int] NOT NULL,
[delivery_address_id] [int] NOT NULL,
CONSTRAINT [PK_order] PRIMARY KEY CLUSTERED
(
[invoice_no] 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].[order]  WITH CHECK ADD  CONSTRAINT [FK_order_address] FOREIGN KEY([billing_address_id]) /******foreign key with address******/
REFERENCES [dbo].[address] ([address_id])
GO
ALTER TABLE [dbo].[order] CHECK CONSTRAINT [FK_order_address]
GO
ALTER TABLE [dbo].[order]  WITH CHECK ADD  CONSTRAINT [FK_order_address1] FOREIGN KEY([delivery_address_id]) /******foreign key with address******/
REFERENCES [dbo].[address] ([address_id])
GO
ALTER TABLE[dbo].[order] CHECK CONSTRAINT [FK_order_address1]
9
GO
ALTER TABLE [dbo].[order]  WITH CHECK ADD  CONSTRAINT [FK_order_customer] FOREIGN KEY([cust_number]) /******foreign key with customer table ******/
REFERENCES [dbo].[customer] ([cust_number])
GO
ALTER TABLE [dbo].[order] CHECK CONSTRAINT [FK_order_customer]
GO
Figure7: order Order_detailstable
CREATE TABLE [dbo].[order_details](
[order_id] [int] NOT NULL,
[invoice_no] [int] NOT NULL,
[item_number] [int] NOT NULL,
[quantity] [int] NOT NULL,
CONSTRAINT [PK_order_details] 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]
10
GO
ALTERTABLE [dbo].[order_details]  WITH CHECK ADD  CONSTRAINT [FK_order_details_item_detail] FOREIGN KEY([item_number]) /******foreign key with item_details table ******/
REFERENCES [dbo].[item_detail] ([item_number])
GO
ALTER TABLE [dbo].[order_details] CHECKCONSTRAINT [FK_order_details_item_detail]
GO
ALTER TABLE [dbo].[order_details]  WITH CHECK ADD  CONSTRAINT [FK_order_details_order] FOREIGN KEY([invoice_no]) /******foreign key with order table ******/
REFERENCES [dbo].[order] ([invoice_no])
GO
ALTER TABLE [dbo].[order_details] CHECK CONSTRAINT [FK_order_details_order]
GO
Figure8: order_details
11
Views.sql Customer View
Figure9: customer view Order view
Figure10: order view Ordered itemview
Figure11: ordered item view Queries.sql Query 1–Item Search
select * from item_detail where price <=30 AND item_name LIKE '%shirt%' ORDER BY price DESC
Figure12: query 1 Query 2–Customer Interests List
SELECT CONCAT (UPPER([Customer view].[full Name]),' is interested in the ', UPPER([item_category].category_name)) AS interests FROM [Customer view] INNER JOIN category_newsletter INNER JOIN item_category on
12
category_newsletter.category_number=item_category.category_number ON [Customer view].cust_number=category_newsletter.cust_number  WHERE [Customer view].newsletter LIKE '%y%'
Figure13: query 2 Query 3–Unpopular Items
SELECT item_detail.item_number,item_detail.item_name,item_detail.price FROM item_detail WHERE item_detail.item_number NOT IN ( SELECT [order_details].item_number from order_details)
Figure14: query 3 Query 4–Order Summary
SELECT TOP (100) PERCENT dbo.[order view].cust_number, dbo.[order view].date_order, COUNT(dbo.[ordered item view].item_number) AS item_ordered, SUM(dbo.[ordered item view].subtotal) AS total_cost FROM     dbo.[order view] INNER JOIN dbo.[ordered item view] ON dbo.[order view].invoice_no = dbo.[ordered item view].invoice_no GROUP BY dbo.[order view].cust_number, dbo.[order view].date_order, dbo.[order view].cust_number ORDER BY total_cost DESC
Figure15: query 4 Query 5–ItemsperCustomer
SELECT TOP (3) PERCENT dbo.[Customer view].cust_number, dbo.[Customer view].[full Name], CASE WHEN SUM(dbo.[ordered item view].quantity) IS NULL THEN 0 ELSE SUM(dbo.[ordered item view].quantity) END AS [total items] FROM dbo.[Customer view] LEFT OUTER JOIN dbo.[ordered item view] ON dbo.[Customer view].cust_number = dbo.[ordered item view].cust_number
GROUP BY dbo.[Customer view].cust_number, dbo.[Customer view].[full Name] ORDER BY [total items] DESC
13
Figure16: query 5 Query 6–Big Spenders
SELECT dbo. [Customer view].cust_number, dbo.[Customer view].[full Name], COUNT(*) AS Number_of_orders, SUM(dbo.[ordered item view].subtotal) AS grand_total
FROM     dbo.[Customer view] INNER JOIN dbo.[ordered item view] ON dbo.[Customer view].cust_number = dbo.[ordered item view].cust_number GROUP BY dbo.[Customer view].cust_number, dbo.[Customer view].[full Name]
Figure17: query6 Query 7–Category Statistics
SELECT item_category.category_name, item_detail.item_number,item_detail.price FROM item_category INNER JOIN item_detail on item_category.item_number=item_detail.item_number
Figure18: query 7 Query 8–Order Timeline
SELECT TOP (100) PERCENT COUNT(dbo.[order view].cust_number) AS number_of_orders, YEAR(dbo.[order view].date_order) AS year, { fn MONTHNAME(dbo.[order view].date_order) } AS month, SUM(dbo.[ordered item view].subtotal) AS total_order_cost FROM     dbo.[order view]
14
INNER JOIN dbo.[ordered item view] ON dbo.[order view].invoice_no = dbo.[ordered item view].invoice_no GROUP BY { fn MONTHNAME(dbo.[order view].date_order) }, YEAR(dbo.[order view].date_order) ORDER BY year, month
Figure19: query 8

Comments

Post a Comment

Popular posts from this blog

Assignment 2 Database Design & Implementation Student Name: Student ID:   Table of contents Assumptions 2 Logical ER diagram 3 Physical ER diagram 4 Data Dictionary 5   Assumptions • The customers who are registered, only their details are recorded. • Each customer has their addresses. Customers can have multiple addresses. • All the customer details are mandatory. • The customer referral number will be from the customer table. • Except for address name, all the address tables are mandatory. • The items which are registered, only their details are recorded in the database. • It is not mandatory to the description of each item. • Each item belongs at least one category. • Each order has a particular invoice number. • It is possible the customer has never ordered anything. • It is possible that particular item has never been ordered. • It is possible that particular category has never been ordered. • It is possible that customer has not been interested in any of the categories.   Logical ER diagram Figure 1: logical diagram The logical diagram has been well designed according to the scenario given. The scenario results in designing of 9 entities with a primary key defined in each table. This diagram has been designed taking 3 NF normalization forms, to remove any inconsistency and redundancy. All the tables are precisely observed and designed while taking some few assumptions. This complete ER-diagram has been converted into Physical ER diagram.   Physical ER diagram Figure 2: Physical ER diagram This physical ER diagram is an actual representation of the database. All the constraints have been perfectly mentioned in the diagram. This physical ER diagram shows all sort of relationships which will exist in the actual database.   Data Dictionary Customer Column Type Null Default Links to Comments cust_number(primary key) int(50) No first_name Varchar(100) No last_name Varchar(100) No email_address Varchar(100) No Y/N int(50) No Indexes Keyname Type Unique Packed Column Cardinality Collation Null Comment primary BTREE yes No cust_number 180 A No unique BTREE yes No email_address 180 A No Referral Column Type Null Default Links to Comments id (primary key) int(50) No referral_number (foreign key) int(50) No customer -> cust_number Indexes Keyname Type Unique Packed Column Cardinality Collation Null Comment primary BTREE yes No id 180 A No Foreign key BTREE no No referral_number 180 A No Cust_address Column Type Null Default Links to Comments Addres_id (primary key) int(50) No Cust_number (foreign key) int(50) No Customer->cust_number Address_name Varchar(100) yes Indexes Keyname Type Unique Packed Column Cardinality Collation Null Comment PRIMARY BTREE Yes No Addres_id 180 A No Foreign key BTREE no No Cust_number 180 A No Item_details Column Type Null Default Links to Comments item_number (Primary) int(50) No Item_name Varchar(100) no description Varchar(100) Yes NULL price int(10) no Indexes Keyname Type Unique Packed Column Cardinality Collation Null Comment PRIMARY BTREE Yes No item_number 57 A No Item_category Column Type Null Default Links to Comments Category_number (Primary) int(50) No Category_name Varchar(100) No Item_number (foreign key) int(50) No Item_details -> item_number Indexes Keyname Type Unique Packed Column Cardinality Collation Null Comment PRIMARY BTREE Yes No Category_number 95 A No foreign key BTREE no No Item_number 100 A No Category_newsletter Column Type Null Default Links to Comments Cust_number(foreign key) int(50) No customer -> cust_number Category_number(foreign key) int(50) No Item_category->category_number Indexes Keyname Type Unique Packed Column Cardinality Collation Null Comment Foreign key BTREE yes No Cust_number 100 A No Foreign key BTREE No No Category_number 100 A No Order Column Type Null Default Links to Comments Invoice_no (primary key) int(50) No Cust_number (foreign key) int(50) No Customer->cust_number Date_order date No Billing_address_id (foreign key) int(50) No Address->address_id Delivery_address_id (foreign key) int(50) No Address->address_id Indexes Keyname Type Unique Packed Column Cardinality Collation Null Comment Primary key BTREE yes No Invoice_no 100 A No foreign key BTREE No No Cust_number 80 A No foreign key BTREE No No Billing_address_id 80 A No Foreign key BTREE No No Delivery_address_id 80 A No Order_details Column Type Null Default Links to Comments order_id (primary key) int(50) No Invoice_no (foreign key) int(50) No Order->invoice_no Item_number (foreign key) int(50) No Item_details->item_number quantity int(50) No Indexes Keyname Type Unique Packed Column Cardinality Collation Null Comment primary key BTREE yes No order_id 100 A No Foreign key BTREE No No Invoice_no 80 A No Foreign key BTREE No No Item_number 80 A No

Assignment 2 Database Design & Implementation Student Name: Student ID: Table of contents Assumptions . 2 Logical ER diagram .. 3 Physical ER diagram .. 4 Data Dictionary . 5 Assumptions ·          The customers who are registered, only their details are recorded. ·          Each customer has their addresses. Customers can have multiple addresses. ·          All the customer details are mandatory. ·          The customer referral number will be from the customer table. ·          Except for address name, all the address tables are mandatory. ·          The items which are registered, only their details are recorded in the database. ·      ...
Read more