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.

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.

Problem 3 it wid o wlocation ='Winterfall' wid = ( h.wid o (wages = 50000-(skill= 'Swordsmanship')) (OFHOUSE HWESTEROSISKILL)) (WESTEROSI) wid o wlocation ='Winterfall' wid = h.wid wages = 50000/-(skill = 'Swordsmanship') (OFHOUSE HWESTEROSISKILLWESTEROSI)

Problem 5 πwname,wlocation wages >50000 kingdom='Kings Landing' A wid = (n wid (WESTEROSISKILL))(WESTEROSI OFHOUSE HOUSE) w.wname,w.wlocation o wages >50000 kingdom='KingsLanding' A w.wid = ws.wid (WESTEROSISKILL ws WESTEROSI WOFHOUSE HOUSE)

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.

25. (SQL Week 1 Question 100-025) You want something small and cute. How many creatures have a height between 10 and 15 inches and a weight between 5 and 10 pounds? (Hint: you should use the BETWEEN command) 26. [SQL Week 1 Question 100-026) Your publisher has decided to reclassify all Knights from Real to Magical with regard to their Realm. Make this update in your database. Before you do this update, you should be able to verify you have 100 creatures with Realm of Magical. After this update, how many creatures have a Magical Realm? If you get an error code 1175 that grumbles that you are using safe update mode, run the following code before your update: SET SQL_SAFE_UPDATES = 0; Note- this command has you modify the database. If you are then going to go back and answer some other questions after you do this command, be sure you refresh your database (i.e, re- load and re-run the script which made the database in the first place) before answering those questions. 27. [SQL Week 1 Question 100-027] DELETE. It's time to delete stuff. Note - this command has you modify the database. If you are going to go back and answer some other questions after you do this command, be sure you refresh your database (i.e. re- load and re-run the script which made the database in the first place) before answering those questions. First, let's verify your database is fully refreshed. Run the following checksum code. (There's a lot of code here; it's best to copy/paste into SQL.) SELECT ((COUNT(*) * AVG(Weight)) MOD 99) as checksum from litcharacters: You should get a checksum answer of 47. Delete all the dinosaurs who have already made an appearance. In other words, delete anything with a Creature SubType of Dinosaur and more than 0 appearances. Run the checksum code again. What is your checksum now? 28. [SQL Week 1 Question 100-028] Note - this command has you modify the database. If you are going to go back and answer some other questions after you do this command, be sure you refresh your database (i.e. re-load and re-run the script which made the database in the first place) before answering those questions. First, check your database is fully refreshed. Run the following checksum code. (There's a lot of code here; it's best to copy/paste into SQL.) SELECT (COUNT(*) * AVG(Weight)) MOD 99) as checksum from litcharacters: You should get a checksum answer of 47. You have some more characters to include. Insert the below characters in to the database: ('Mogwai', 'Magical', 'Other', 'Fairy', 'Female', '25', "158","2"), ('Menehune', "Real", "Human", "Superhero', "Male", "56", "195', '5')

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?

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?