2. What normal form is this design? Why? What should be done to

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

CSE5330-005 Fall 2022 Project 1 In this project, you will get started on how to use a relational DBMS. You can either use the ORACLE RDBMS, or the MySQL system. You will use the interactive SQLPLUS facility, and the SQL programming facility, by creating tables, populating them with data, and querying and updating the tables. You should do the following: 1. Create the following tables for the US COVID-19 database whose schema is specified in Page 3 of this document: STATE, COUNTY, CONFIRMED CASES, DEATHS, VACCINATIONS. Write your CREATE TABLE statements in a text file and execute the commands from the file through SQLPLUS. You should capture the execution in a spool file that will be turned in. Specify appropriate key and referential integrity constraints. The data types for each attribute are given after the schema diagram. 2. Write one or more database programs to load the records that will be provided to you into each of the tables that you created. The supplied data is synthetic and may not represent the ground reality. You might have to pivot some data to suit to the schema given. You can use any programming or scripting language you are familiar with (JAVA with JDBC, Pro*C, PERL, PHP, Python, etc.). 3. Write down the queries for the English queries that are listed later. Execute each query and display its results. Capture your commands in spool files for turning in. 4. Execute 3 more Insert commands that attempt to insert 3 more records, such that the records violate the integrity constraints. Make each of the 3 records violate a different type of integrity constraint. Capture your commands in spool files for turning in. 5. Execute a SQL command to Delete a record that violates a referential integrity constraint. Capture your command in a spool file for turning in. 6. Repeat 5 but Insert three new records that do not violate any integrity constraints. Capture your commands in spool files for turning in. You should turn in to the GTA one or more Spool files as part of the assignment, including creating the tables and the query results. You should also turn in the source code for the programming part of the assignment for loading the data. Document your output when needed by writing down an explanation for each step (by editing the spool file); for example, explain the integrity constraints violated in item 5.

Part 4 - Single Table Queries 1. [SQL Week 1 Question 100-001] SELECT just the Name and Gender columns from litcharacters. Order ascending by idLitCharacters (you may display the idLitCharacters if you like but it is not required. You need to sort by that field.) SELECT Name, Gender FROM litcharacters ORDER BY idLitCharacters; 2. [SQL Week 1 Question 100-002] List the different creature types are there in the litcharacters database? For example, if you had three humans and one animal, that would be two types - human and animal. SELECT DISTINCT Creature from litcharacters: 3. [SQL Week 1 Question 100-003] Make output which includes only the Name, Creature Type, and Cre SubType for all the Animals. Order by ascending idLitCharacters (you may display the idLitCharacters if you like but it is not required. Whether or not you display the idLitCharacters field, you need to sort by it.) SELECT Name, CreatureIvoe. CreatureSubType FROM Jitcharacters Order By idLitCharacters ASC; 4. [SQL Week 1 Question 100-004] Your editor wants a list of the Name, Creature Type, Creature SubType and Gender for all new creatures - those who have not made an appearance yet (so their Appearance total is 0.) Order by ascending idLitCharacters (you may display the idLitCharacters if you like but it is not required. Whether or not you display the idLitCharacters field, you need to sort by it.) SELECT Name, creatureType, CreatureSubtype, Gender FROM Litcharacters WHERE 'appearance- O' ORDER BY idLitCharacters ASC; 5. [SQL Week 1 Question 100-005] Now your editor wants a list of the Name, Creature Type, Creature SubType and Gender for all well-worn creatures - those who have made 5 or more appearances. Include those who have made exactly 5 appearances. Order by ascending idLitCharacters. SELECT Name, creatureType, CreatureSubtype, Gender FROM litcharacters WHERE appearances >= 5 ORDER BY jdLitCharacters ASC; 6. [SQL Week 1 Question 100-006] They want to know the mid-popular ones as well. Create a list of the Name, Creature Type, Creature SubType and Gender for all mid-popular creatures - those who have made between 3 and 5 appearances. Include those who have made exactly 3 and exactly 5 appearances. Order by ascending idLitCharacters.

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

Queries 7.) Group-By: Create a query that shows the CookieName, and total Quantity sold for cookies with a total quantity of more than 10 boxes. Save the query as CookieCount (5) 8.) Group-By: Create a query that shows the CustomerID, FirstName, CookieName and total Quantity for all orders with "Lemonade" cookies in them. Save the query as LemonadeOrders. (5) (Hint: "Lemonade" is a specific name) 9.) Group-By: Create a Query that shows OrderID, CookieName, total Quantity, Price and a new field named "Line Total" that calculates the Quantity*Price for line items. Name it Sales. (5) (Hint: This is similar to query # 14 that used the Date Diff() function, however you do not need a function for this query. Just a simple multiplication.) 10.) Create an Update query that increases the price of all cookies by $1. Name the Query Price Update (5)

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.

WHAT ARE YOU REQUIRED TO DO? 1. Develop a conceptual data model based on the above Narrative. You may use the Chen's notation, the Crow-feet notation, or the Unified Modeling Language notation to present the conceptual data model. A summary of steps for developing the conceptual data model is given here: a. Identify entities, their associated attributes, and primary key attribute(s). b. Identify the relationships between entities and the associated relationship attributes and cardinality (including multiplicity and participation constraints). c. Produce an Entity-Relationship Diagram depicting the conceptual data model. 2. Produce a logical data model from the conceptual data model you have developed in (1). A summary of steps for developing the logical data model is given here: a. Transform the Entity-Relationship Diagram into relational schemas (1.e., tables). b. Refine the relational schemas by combining schemas and normalizing the tables where necessary to produce the final logical data model. 3. Present the conceptual data model and logical data model in a Powerpoint presentation file. 4. Submit the Powerpoint presentation file.

1. Suppose relation R(A,B,C) has the tuples: (8 pts) ABC 123 123 231 312 223 233 Using bag projection and intersection, compute PROJAB(R) INTERSECT RENAMES(AB)(PROJB,C(R)). Note that the renaming is only to give the two projections the same schema.

7. [SQL Week 1 Question 100-007] Get a listing of the id number (idLitCharacters), Name, Creature SubType for all the pirates, mermaids, and fairies. Only include these three Subtypes. Order by ascending idLitCharacters. 8. [SQL Week 1 Question 100-008] You need a big scary one. Get a listing of all the names of Basilisks which have a weight greater than 475. Make sure you print the ID, the name, the Creature SubType, and the weight. Order by ascending idLitCharacters. 9. [SQL Week 1 Question 100-009] You want to get some name ideas for somebody who is either a mad scientist or supervillain. What are those names? Print out the Lit Character ID as well as the name and Sub Type. Order by ascending idLitCharacters. 10. [SQL Week 1 Question 100-010] You need a list of non-male creatures. Select the idLitCharacter, Name, Creature SubType, and Gender for everybody whose gender is not designated as Male. Order by ascending idLitCharacters. Take a screenshot of the top of your output. Then scroll down until you see ID 51, Amara the Mermaid. Take a screenshot of Amara and the rows immediately beneath her as well. 11. [SQL Week 1 Question 100-011] Select the idLitCharacters, name, Gender, Creature SubType. Your newest author wants a specific list of names which meet one of two criteria. The first criteria to meet would be a female pirate. The second criteria to meet would be any cute tiny male creature of weight 10 or below. Your output should contain some female pirates and some cute tiny male creatures. Sort the results first alphabetically on Gender and then within that field by idLitCharacters ascending. 12. [SQL Week 1 Question 100-012] Display the idLitCharacters, name, Gender, Creature SubType, weight, and height for everybody whose height is greater than or equal to 200. Sort it so the heaviest ones are on the top, and within the weight then ascending by idLitCharacters. 13. [SQL Week 1 Question 100-013] You want the big ones. Make a list of all the ID, the name, the Creature SubType, and weight and height. You only want those whose weight is greater than or equal to 150 or those whose height is greater than or equal to 65. Sort your results alphabetically by Name and then ascending by idLitCharacter . 14. [SQL Week 1 Question 100-014] You want to know for all humans whose realm is Magical, what is the maximum weight in pounds?

15. [SQL Week 1 Question 100-015] What is the lightest weight of any of the creatures who is taller than 60 inches? 16. [SQL Week 1 Question 100-016] How many creatures are of type Human? 17. [SQL Week 1 Question 100-017] Get the average weight of all human type creatures. Note your answer to four decimal places. 18. [SQL Week 1 Question 100-018] Your newest novel has a scene in which all the miscreants are on a bridge, but collectively they are too heavy for it so the bridge fails. It's very exciting for your audience. What is the sum of the weight of all the miscreants here? We define miscreants here as Pirates, Supervillains, Mad Scientists, and Cowboys of any gender. 19. [SQL Week 1 Question 100-019] You are going to have a scene in which all the basilisks are on one side of a balance scale, and the single smallest dinosaur is on the other side of the scale. The dinosaur is bigger, of course. By how many pounds? (It's OK if you need to run two separate queries and then manually subtract them.) 20. [SQL Week 1 Question 100-020] Use the LIKE command to select the idLitCharacters, name, and creature subtype of all the creatures which start with MER (i.e. mermen, mermaids, etc.). Sort it alphabetically by name. 21. [SQL Week 1 Question 100-021] What is the average weight of all creatures for which the name contains the string 'us'? The phrase can be anywhere in the name - so "Usman" would count, as would "Beusaleth" and "McManus." (Your database may vary; these names may or may not be in your version of the database.) Note your answer to four decimal places. 22. [SQL Week 1 Question 100-022] Select all the fields for all the creatures that contain the word "the" in their names, such as "Roderick the Brave." Do not include entries for which "The" is the first word, such as "The Fire Breather." Order alphabetically by Name, then ascending by idLitCharacters. 23. [SQL Week 1 Question 100-023] List all creatures which have two consecutive L's anywhere in their name (such as "Elling" or "Llewers" or "Dingell") and which are not Knights. Sort by number of appearances (fewest appearances on top) and then ascending by idLitCharacters. 24. [SQL Week 1 Question 100-024] Use the SQL IN command to select all fields from all creatures which are Knights, Pirates, Fairies, or Jackalopes and who have had 1 or more appearances to date. Sort it by idLitCharacters, this time in DESCENDING order, so the highest ID is on the top. Use the SQL LIMIT command to display only 10 results. How many of each type were in your top 10 results?