2. Get the capacity and name of the oldest project [15]. a. Hint: start by sorting the projects in the increasing order of their start dates, so that the oldest one is at the top. After making sure that you have the projects in the right order, modify the query to print only the first row (use limit clause).

Question 3 Given a relational R with a function dependency: F = X →Y Answer the following questions: 1. If X is non-prime attribute and Y is prime attribute, select the right answer: a. R is not in 2NF b. R is in 2NF, but not 3NF c. R is in 3NF, but not BCNF d. R is in BCNF 2. If X is a subset of Candidate Key and Y is non-prime attribute, select the right answer: a. R is not in 2NF b. R is in 2NF, but not 3NF c. R is in 3NF, but not BCNF d. R is in BCNF 3. If X is non-prime attribute and Y is non-prime attribute, select the right answer: a. R is not in 2NF b. R is in 2NF, but not 3NF c. R is in 3NF, but not BCNF d. R is in BCNF 4. If X is Candidate Key attribute and Y is prime attribute, select the right answer: a. R is not in 2NF b. R is in 2NF, but not 3NF c. R is in 3NF, but not BCNF d. R is in BCNF

1. Design a database to keep track of information for an art museum. Assume that the following requirements were collected: The museum has a collection of ART_OBJECTS. Each ART_OBJECT has a unique Id_no, an Artist (if known), a Year (when it was created, if known), a Title, and a Description. The art objects are categorized in several ways, as discussed below. . ART_OBJECTS are categorized based on their type. There are three main types: PAINTING, SCULPTURE, and STATUE, plus another type called OTHER to accommodate objects that do not fall into one of the three main types. • A PAINTING has a Paint_type (oil, watercolor, etc.), material on which it is Drawn_on (paper, canvas, wood, etc.), and Style (modern, abstract, etc.). A SCULPTURE or a statue has a Material from which it was created (wood, stone, etc.), Height, Weight, and Style. ART_OBJECTS are categorized as either PERMANENT COLLECTION (objects that are owned by the museum) and BORROWED. Information captured about objects in the PERMANENT_COLLECTION includes Date_acquired, Status (on display, on loan, or stored), and Cost. • Information captured about BORROWED objects includes the Collection from which it was borrowed, Date_borrowed, and Date_returned. • Information describing the country or culture of Origin (Italian, Egyptian, American, Indian, and so forth) and Epoch (Renaissance, Modern, Ancient, and so forth) is captured for each ART_OBJECT. The museum keeps track of ARTIST information, if known: Name, DateBorn (if known), Date_died (if not living), Country_of_origin, Epoch, Main_style, and Description. The Name is assumed to be unique. Different EXHIBITIONS occur, each having a Name, Start_date, and End_date. EXHIBITIONS are related to all the art objects that were on display during the exhibition. • Information is kept on other COLLECTIONS with which the museum interacts, including Name (unique), Type (museum, personal, etc.), Description, Address, Phone, and current Contact person. Draw an EER schema diagram for this application. Discuss any assumptions you make, and that justify your EER design choices. Map the following EER diagram from the above step into a relational database

1. A detailed schema using the ER model including: • Design a corresponding set of tables that are in at least 3NF. • List of the attributes for each entity and relationship. • Explanations of the non-obvious entities and relationships. 2. Provide DDL including: CREATE tables. 3. Construct the following queries and provide results: A) Populate (INSERT Statements) tables with some data. B) Show customer details i.e., customer information and address (pick any)? C) Show all Products that are below 50 quantity. D) Show all orders of a customer (pick any)?

Project #2 Description In this project, you will design and implement a database for a Bank Enterprise to reflect the following scenario. You will first design an ER/EER schema diagram for this database application. Then, you will map the EER schema into a relational database schema and implement it on ORACLE/MySQL in Omega. Finally, you will load some data into your database, and create some queries and update transactions. The bank is organized into branches. Each branch is located in a particular city and is identified by a unique name. The bank monitors the assets of each branch, Part 1 - You will first design ER/EER schema diagram based upon the above Bank database requirements and create the schema diagram and documentation report describing your design choices. As part of this assignment, you should identify any missing or incomplete requirements, and explicitly state them in your documentation. You should also explicitly state any assumptions you made that were not part of the requirements listed above.

Part 2 - The second part of the assignment will be to map the EER schema design to a relational database schema, and create the tables corresponding to the relational schema using the ORACLE/MYSQL DBMS. You will add to your report a listing of the CREATE TABLE statements. Specify as many constraints (key, referential integrity) as you can in the relational schema. You should state the choices you made during the ER/EER-to-relational mapping, and the reasons for your choices.

PART 2: You must have the Microsoft Access database system installed for this part. Using your physical model, create a database in Microsoft Access. Each entity in your diagram represents a table object in your database. Start by creating a new, blank database.

9. Write a SELECT statement based on the tables in the third normal form from your response to Question 2. Include at least 1 join and 1 filter clause.

3. Given the following set of functional dependencies, use Armstrong's axioms to derive (i), (ii), and (iii). 1. X→ Y 2. Z→Y 3. V → XYZ 4. XZ → V (1) (ii) XZ → YV (iii) XZ → VXYZ V → VXYZ Write each derivation (proof), justifying each step, the same way we do it in the presentation (e.g. see slide 9). Don't forget to write which axiom is used for each step.

Creating Your First ERD There are some tricks and must do's when creating an ERD. Click the following link as we walk through some of the pertinent steps before starting your lab. Creating Your First ERDB