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 1. Given a relation R (A, B, C, D, E, F) in 1 NF and its functional dependency set: F={A→ CE, E-F.D→ B). (i) Find candidate key of R (i) Decompose R into 3NF Submit Q1.pdf/Q1.docx file. Please Show all the working. Do not simply write answers. If essential steps, such as edge diagrams, transitive inferences are missing, points will be deducted.

Question 2. Given a relation R (A, B, C, D, E, F) in 1 NF and its functional dependency set: F={B → CD, E → A. C→ DF). (i) Find candidate key of R (1) Decompose R into BCNF

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)?

Question #3. Write a PHP script for each of the following questions: (a) Suppose you are given an array color with some values. Write a PHP script to get the first element of the array.

5. Create an ERD for a chain of Candy Stores. They will want to track customer, product, employee, store location, sale amount, discount type (if any), discount amount (if any). Be sure to include cardinality via crow's feet as well as identified PK and FK for each table. No other sample fields are necessary.

1. Defined below are three relations' schemas and their sets of functional dependencies. R₁(A, B, C, D) A → BCD C → AB R₂(E, F, G, H, I, J, K) E → FG EFG H R₁(L, M, N, O, P, R) L MN LN → OPR R → LM a) For each of the relations R₁, R₂, and R3, check which of the following are candidate keys (can be more than one for each relation, or none). R₁: A, B, C, D, AB, BC, ABC R₂: E, FG, EFG, EIJK R₁: L, N, R, LN, MR, NMR b) For each of the relations R₁, R₂, and R, determine if it is in 3NF. c) For each of the relations R₁, R2, and R3 determine if it is in BCNF.

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