Posts

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...............................................................................................................
Post a Comment
Read more

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. ·      ...
Post a Comment
Read more