Assignment 3: Design a Database

Setting

Although you always wanted to be an artist, you ended up being an expert on databases because you love to cook data and you somehow confused database with data baste. Your old love is still there, however, so you set up a database company, ArtBase, that builds a product for art galleries. The core of this product is a database with a schema that captures all the information that galleries need to maintain.

• Galleries keep information about artists, their names (which are unique), birthplaces, age, and style of art.
• For each piece of artwork, the artist, the year it was made, its unique title, its type of art (e.g., painting, lithograph, sculpture, photograph), and its price must be stored.
• 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.
• Each group is identified by a name (like those just given) that describes the group.
• Finally, galleries keep information about customers. For each customer, galleries keep that person’s unique name, address, total amount of dollars spent in the gallery (very important!), and the artists and groups of art that the customer tends to like.

Your Tasks/Questions.

Q1. ER diagram (50 pts.) Give an ER diagram for the database. Be sure to write down all the the assumptions you have made.

Q2. Functional Dependencies (20 pts.) List all the FDs that hold for the database.

Q3. Relational Schema in Data Definition Language (20 pts.) Translate your ER diagram into a relational schema in the form of SQL DDLs. Choose appropriate data types for each attribute and include primary key and foreign key constraints.

Q4. Normal Forms (10 pts.) For each relation in your relational schema, write down whether the relation is in BCNF or 3NF, and if not give a violating FD.

Submission instructions

• Only electronic submissions via Laulima will be accepted.
• This is an individual assignment.
• Every student must make a submission to receive credit. However, you may discuss the problem with at most two other fellow students, but you must write the answers up yourself. List all fellow students that you had discussions with in your PDF submission.
• Label the answers to the four questions clearly.
• Type and/or draw the answers to all four questions using a word processor (eg Microsoft Word).
• Use the diagramming features of the word processor to draw the ER diagram.
• Export the document as a PDF file.
• In addition to the PDF file, submit a text file (named with a .sql suffix) containing all the DDL(s) that is needed for the creation of the database. This DDL file should be executable as a script using the Oracle DBMS command line processor.
• You should name your submission files -a2.{pdf,sql}. For example, if your UH email address is john@hawaii.edu, your submission should contain the following two files: (1) john-a2.pdf contains answers to all 4 questions of the assignment. (2) john-a2.sql contains the DDL from question 3.
• Make sure that the PDF submission also contain your name and list of collaborators.