Chris Pollett>
CS157a |
HW#2 --- last modified September 22 2023 20:06:23.Due date: Oct 4 Files to be submitted: Purpose: Related Course Outcomes: The main course outcomes covered by this assignment are: CLO1 (Course Learning Outcome 1) -- Explain basic database concepts, including the structure and operations of the relational data model. CLO3 Conduct normalization to decompose relations into 3NF or BCNF when that removes anomalies. CLO4 Use SQL as a data definition language (DDL) to create and alter databases, tables, views, and indexes CLO5 Use SQL as a data manipulation language (DML) for querying and modifying databases Specification: This assignment consists of two parts a written assignment and a (SQL) coding assignment. Files for both should be submitted in the Hw2.zip file you submit. Remember to include a readme.txt file listing your teammates. For the written exercises, I want you to do the following problems as modified from the class text (you only have to do the parts without exclamation marks):
Next for each of the relations in Problem 1 suggest which columns would make a good primary key. Also, suggest SQL types for each of the columns. Write this up in Hw2.pdf. Then create in Mysql each of these tables with the primary keys and types you describe. Submit in a create.sql file your table creation scripts. Next insert the rows listed in this problem. Use \h to see how to do execute statements from a text file in Mysql. Submit your insert statements as a file insert.sql file . The files create.sql and insert.sql will be auto-evaluated by the grader's code so can get immediate feedback on them if you do a submission, even if the rest of Hw is not yet submitted. Submit in a transcript.txt file a transcript of your sessions where you are using these file to create and populate this database.
|