Homework SourseA comprehensive entityrelationship diagram ERD o This must i
A comprehensive entity-relationship diagram (ERD) o This must include an appropriate set of attributes for each entity. A relational model o Your relational model should be logical, should reflect strong understanding of the overall function of your database, and must comply with each of the requirements laid out in the ERD. (All of what i have done is attached to this point)
A functional dependencies diagram normalized into 3NF o Based upon the ERD you create, devise dependency diagrams and normalize the data into 3NF. o This step will require you to compile a collective list of all the functional dependencies in order to create the model. o The data in this section must reflect the list of functional dependencies and must be accurately normalized into 3NF. A functional relational schema normalized into 3NF o Based upon the ERD and the dependency diagrams, create the relational schema and normalize the data into 3NF.
Here is what I have:
Relational Model – Tables
Stores Table:
Column Name
Data Type
Allow Nulls
Index
Store_ID
Integer
No
Primary Key(PK)
Store A
Varchar(50)
No
Store B
Varchar(50)
No
Customer_ID
Integer
No
Foreign Key(FK)
Product_ID
Integer
No
Foreign Key(FK)
Book_ID
Integer
No
Foreign Key(FK)
Order_ID
Integer
No
Foreign Key(FK)
Books Table:
Column Name
Data Type
Allow Nulls
Index
Book_ID
Integer
No
Primary Key(PK)
Title
Varchar(50)
No
ISBN
Varchar(50)
No
PublicationYear
Varchar(50)
No
Price
Decimal(4,2)
No
Condition
Varchar(50)
No
Author_ID
Integer
No
Foreign Key(FK)
Publisher_ID
Integer
No
Foreign Key(FK)
Genre_ID
Integer
No
Foreign Key(FK)
Store_ID
Integer
No
Foreign Key(FK)
Author Table:
Column Name
Data Type
Allow Nulls
Index
Author_ID
Integer
No
Primary Key(PK)
FirstName
Varchar(50)
No
LastName
Varchar(50)
No
Book_ID
Integer
No
Foreign Key(FK)
Publisher Table:
Column Name
Data Type
Allow Nulls
Index
Publisher_ID
Integer
No
Primary Key(PK)
PublisherName
Varchar(50)
No
Address
Varchar(50)
No
City
Varchar(50)
No
Country
Varchar(50)
No
PhoneNumber
Varchar(50)
No
Book_ID
Integer
No
Foreign Key(FK)
Genre Table:
Column Name
Data Type
Allow Nulls
Index
Genre_ID
Integer
No
Primary Key(PK)
Description
Varchar(50)
No
Product Table:
Column Name
Data Type
Allow Nulls
Index
Product_ID
Integer
No
Primary Key(PK)
Price
Decimal(4,2)
No
Store_ID
Integer
No
Foreign Key(FK)
Customer Table:
Column Name
Data Type
Allow Nulls
Index
Customer_ID
Integer
No
Primary Key(PK)
FirstName
Varchar(50)
No
LastName
Varchar(50)
No
StreetNumber
Varchar(50)
No
StreetName
Varchar(50)
No
PostalCode
Char(5)
No
State
Char(2)
No
Country
Varchar(50)
No
PhoneNumber
Varchar(50)
No
EmailAddress
Varchar(50)
No
Order_ID
Integer
No
Foreign Key(FK)
Store_ID
Integer
No
Foreign Key(FK)
Order Table:
Column Name
Data Type
Allow Nulls
Index
Order_ID
Integer
No
Primary Key(PK)
OrderDate
Date
No
Subtotal
Decimal(4,2)
No
Shipping
Decimal(4,2)
No
TotalCost
Decimal(4,2)
No
Customer_ID
Integer
No
Foreign Key(FK)
Store_ID
Integer
No
Foreign Key(FK)
Product_ID
Integer
No
Foreign Key(FK)
Book_ID
Integer
No
| Column Name | Data Type | Allow Nulls | Index |
| Store_ID | Integer | No | Primary Key(PK) |
| Store A | Varchar(50) | No | |
| Store B | Varchar(50) | No | |
| Customer_ID | Integer | No | Foreign Key(FK) |
| Product_ID | Integer | No | Foreign Key(FK) |
| Book_ID | Integer | No | Foreign Key(FK) |
| Order_ID | Integer | No | Foreign Key(FK) |
Solution
Store
Store_ID
Store A
Store B
Book
----------------------Book_ID
Title
ISBN
PublicationYear
Price
Condition
Author
Author_ID
First name
Last name
Publisher
Publisher_id
Publisher name
Address
City
Country
Phone number
Genre
Genre_ID
Description
Customer
Customer id
First name
Last name
Street number
Street name
Postal code
State
Country
Phone number
Email adderss
Product
product_ID
price
Order
Order_id
Order date
Sub total
Shipping
Total cost
er diagram releation ships between tables:
Store to customer one to many
Store to products one to many
Store to books one to many
Store to Oder one to one
Book to author many to many
Book to publisher one to one
Order to customer one to one
Order to product one to one
