Need right answer Big City Bookstore Case Scenario Big City Bookstore is a young

Question

Need right answer Big City Bookstore Case Scenario Big City Bookstore is a young book store with a growing customer base. They are needing to develop a database to store information about customers, books, book orders by customers, customer types and accounts, and the company's sales reps. The following tables are examples of the information that they have been collecting in an Excel workbook. You have been asked to create an ER diagram using this information to help them create a database to be used for selling books. Customer Account CustID CustFirstName CustlastName AccountNo.AccountNo AccountType SalesReplD 010839Personal 111248 061544 600 601 602 Smith 010839 111248 061544 102 Sue Education CustomerAddress ZipCode Address ype ZipCode City 100 100 N. Broadway 63701 Billing 100 219 Main Street 63701 Shipping 101 One North Street 62901 Billing 63701 63755 62901 Cape Girardeau Jackson Carbondale Mo Mo 101 600 Broadway 62901 Shipping SalesRep OrderiD OrderDate BookiD Quantity CustiD 000 9/2/2018 b4001 000 9/2/2018 b4003 3001 9/6/2018 b4002 1 3001 9/6/2018 b4003 1 SalesReplD SRFirst Name 600 SRLastName West East North We 2 100 101 101 102 Miller 602 Trent 30029/6/2018 b40055 Book BookID Title b4001 Database Design b4002 Hospital Systems b4003 Healthcare Data b4004 Medical Office ProceduresWilliam Anderson Author Mark Smith Judy Wilson Lisa Wynn Publisher wiley 20.00 12.00 Prospect Press 15.00 18.00 10.00 b4005 Compliance and Security Scott Laudon Pearson

Explanation / Answer

let's first see which are the primary keys in each relations or tables:-

1).Customer

in this table, custID can be treated as primary key because clearly in each tuple it is unique.so it determines each row uniquely.

also custFirstName,custLastName and AccountNo are also unique in each tuple so this can also be treated as primary keys but since in one table only one primary key is allowed these all are alternate keys.but custNames are generally not taken as primary keys because they can be same however for this particular scenario it is not.so,it is better to take it as alternate key.

2).Account:-

in this table, AccountNo can be treated as primary key because clearly in each tuple it is unique.so it determines each row uniquely.

SalesRepID is also unique in each tuple so this can also be treated as primary keys but since in one table only one primary key is allowed it is an alternate key.

primary key = AccountNo

alternate key = SalesRepID

3).CustomerAddress:-

in this table, Street can be treated as primary key because clearly in each tuple it is unique.so it determines each row uniquely.

There is no other single key in this table that can be treated as primary keys.however combination of custID,ZipCode and AddressType can be treated as primary key but in table only one primary key is allowed so it is alternate key.

primary key = Street

4).ZipCode:-

in this table, ZipCode can be treated as primary key because clearly in each tuple it is unique.so it determines each row uniquely.

City is also unique in each tuple so this can also be treated as primary key but since in one table only one primary key is allowed it is an alternate key.

primary key = ZipCode

Alternate key = City

5).SalesRep:-

in this table, salesRepID can be treated as primary key because clearly in each tuple it is unique.so it determines each row uniquely.

also SRFirstName,SRLastName and region are also unique in each tuple so this can also be treated as primary keys but since in one table only one primary key is allowed these all are alternate keys.but Names are generally not taken as primary keys because they can be same however for this particular scenario it is not.so,it is better to take it as alternate key.same goes regions as well.

primary key = salesRepID

alternate key = SRFirstName,SRLastName,Region

6).BookOrders:-

clearly in this a single attribute doesn't determine each tuple.so here primary key must be compound key.

so,OrderID and BookID forms a unique pair so compound key {OrderID,BookID} can be treated as primary keys.

other compound keys that can be treated as primary keys are {OrderDate,BookID},{Quantity,BookID}

{CustID,BookID}

7).Book:-

in this table, BookID can be treated as primary key because clearly in each tuple it is unique.so it determines each row uniquely.

also Title,Author and price are also unique in each tuple so this can also be treated as primary keys but since in one table only one primary key is allowed these all are alternate keys.but Author names are generally not taken as primary keys because they can be same as one author could write two books. however for this particular scenario it is not.so,it is better to take it as alternate key.

Now,lets talk about the foreign keys:-

when any attribute in one table references primary key in its own table or another table then we say that attribute is foreign keys.that values inside that attributes must be subset of values inside primary key that is being referenced.

Clealy ,CustomerAddress's CustID and BookOrders's CustID references to primary key of Customer.so

both CustomerAddress's CustID and BookOrders's CustID are foreign keys.

Customer's AccountNo is clearly referencing to Account's primary key AccountNo.so,Customer's AccountNo is foreign key.

CustomerAddress's ZipCode is clearly referencing to ZipCode's primary key ZipCode.so,CustomerAddress's ZipCode is foreign key.

BookOrders's BookID is clearly referencing to the Book's primary key BookID so,BookOrders's BookID is foreign key.

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.