ER Model & Relational Model Solution

1.  (20 Points, 5 Points each) Imagine you are designing a Database for an Al­Harthi Restaurant. The Database will contain information about its Customers (identified uniquely by customer id: custid) and the Products they bought (identified uniquely by product id: pid). For simplicity, each customer­product       purchase   will   be   recorded   with   a   Transaction (identified uniquely by transaction id: tid). For each situation below, draw an ER diagram that fits the most.

a.  The CEO, wants to keep the history of all transactions information made by each customer.  For example,  Saif bought shawarma on transaction 1 and bought another shawarma on transaction 2. The database should keep track both purchases.

b.  To save storage  space,  the CEO only want the Database to keep track one transaction for each customer­product relationship. For example, Saif bought a shawarma on transaction 1 and bought another shawarma on transaction 2. We will only keep track one Saif­shawarma relationship. However, if he bought kabsa on transaction 3, we will keep one Saif­shawarma relationship and one Saif­kabsa  relationship.  Note that which transaction to be kept is not part of the ER design  as it will be part of the business logic design.

Problem c and d will follow the approach introduced in Problem b where the database will only keep track one customer­product relationship.

c.  The Al­Harthi Restaurant gets greedy because of the CEO. Now, to get more profit,  every customer registered in the database must buy at least one  product on each transaction.

d.  Since the restaurant is a hit now, the CEO wants to a new restriction so   that   everyone   can  eat  their  wonderful   food.  Now,  every customer in the Customer database must buy exactly one product on each transaction.

 

2.  (10 Points) An HR, Saif, of an Al­Harthi Bookstore has noticed that you are currently  taking CSci 4707 and learning ER models at the U. Impressed with your background, they want you to be in charge of designing their database.  After talking  with the company’s representative, this is what they want in their database:

a.  A book has one name, one publisher, one ISBN which is used to uniquely identify the book, and may have more than one authors.

b.  A publisher, uniquely identified by its id, may publish zero or more books.

c.  An author, uniquely identified by his/her id, may also publish zero or more books.

d.  Each  book  can  belong  to  none  or  many  categories  and  each category can have zero or many books as well.

e.  Each category forms a hierarchy. Every category can have zero or more categories  as its sub­category. However, each category can only have at most one parent category. For example, “Sports” category has “Football” and “Soccer” categories as its sub­category. However,  “Football” can only have one parent category, which is “Sport”.

Draw the ER model that captures the above requirement.

 

 

3.  (10 Points) Imagine you are drawing an ER diagram for Onestop. You want to keep track of professors, courses they teach, sections, TAs and homeworks. Every professor has a netID, name and office number. Every TA has a netID and a name. A course has a courseID and title. A section has a section meeting time and meeting room, but multiple sections for the same course may meet at the same time (or in the same room). Each course has a number of sections, but a section is associated with only one course. Every professor teaches at least one course, and every course is taught by at least one professor. Every course has zero or more sections, and every section is covered by one or more TAs. (You may ignore the constraint that no TA can cover two sections that meet at the same time.). A  homework  has  a  unique  homework  number  and  non­unique  topic every homework is created by exactly one person who can be either a professor or a TA. Draw the ER diagram for the above scenario!

 

4.  (10 Points) Saif had a dream that when he grows up, he wants to be an artist. Unfortunately, he ended up being an expert on database because he loves to cook data. But, his old love is still alive and he wants to set up a database company, ARTacle, that helps people in creating database for art galleries. A CEO from an art gallery comes to him and presents his idea about his gallery as follows:

a.  The gallery  will keep information about artists (name, birthplace, age, and style of art) and is uniquely identified by name.

b.  For each piece of artwork, the artist, the year it was made, its title, its type of art, and its price must be stored. Each artwork is uniquely identified by its title.

c.  Pieces of artwork are also classified into groups of various kinds, for example, portraits, still lifes, works by Picasso, or works of the 19th century;  a given piece may belong to more than one group.

d.  Finally,   gallery   keep   information   about   customers.   For   each customer,  gallery  keep that person’s unique name, address, total amount of dollars spent in the gallery, and the artists and groups of art that the customer tends to like.

Draw an ER diagram that Saif should draw for the database.

 

 

B. (50 Points) Relational Model

 

 

1.  (20 Points, 5 Points each) Translate A1 Problem (a­d) into a correct SQL Tables including the needed constraints. Write the correct CREATE TABLE SQL command for each table you created (You don’t need to draw the table).

 2.  (10 Points) Translate Homework 1 A2 Problem into a correct SQL Tables including the needed constraints. Write the correct CREATE SQL command for each table you created (You don’t need to draw the table).

 
3.  (10 Points) Translate Homework 1 A3 Problem into a correct SQL Tables including the needed constraints. Write the correct CREATE SQL command for each table you created (You don’t need to draw the table).

 

4.  (10 Points) Translate Homework 1 A4 Problem into a correct SQL Tables including the needed constraints. Write the correct CREATE SQL command for each table you created (You don’t need to draw the table).