Homework 6: Table creation and SQL queries
Database Systems
Spring, 2020
Due: April 6 (11:59 pm EDT)
Some questions on homework 6
This homework is based on homework 5, in which you designed a
relational schema for a database. The objective of this
assignment is to implement your schema by creating a set of tables
using the MySQL DBMS on sql.cs.oberlin.edu.
part one. First, make
any necessary corrections to your E/R diagram and relational
schema. Make sure that your relations are in BCNF (or at least
3NF). Apply the decomposition procedure if necessary.
Is each relation in your schema in Fourth Normal Form (4NF) with
respect to multivalued dependencies? If not, decompose the
relation into smaller relations so that each relation is in 4NF.
part two. Once you are
satisfied with your relational design, it is time to implement the
database by creating tables and inserting a set of sample tuples in
each one. To do this create an SQL command file (with
extension .sql) for each table containing:
- A comment section with your name, the date, and a description
of the .sql file.
- A drop statement to drop any existing table with the same
name, before you create it.
- A create statement to create the table. This should list
the table's attributes and identify the primary key and
any foreign keys.
- A describe statement which will display the structure of the
table as you created it.
- A series of 8-10 insert statements to insert data in the
table.
An alternative way to insert data in the database is to use the LOAD
DATA INFILE command, which is what I used for the moma and imdb
databases. It is used to load data from a tab- or
comma-delimited text file (.tsv or .csv), which can be created
manually or exported from a spreadsheet or other application.
As a guide, I'm placing the files I used to set up the moma database
in a folder that you can access through the course
website: http://www.cs.oberlin.edu/~jdonalds/311/moma/
(Look at loadartists.sql
and loadproduced.sql.
You shouldn't need to do anything as complicated as
loadartworks.sql)
When you create your database, use the database name I've assigned
to you: the name is the same as your username.
part three. Write
several (8-10) SQL queries to illustrate how your database could
be used in practice. Put each in a separate .sql file.
You should try to use most or all of the SQL constructs discussed
in class and in the textbook (joins, subqueries, aggregates,
grouping, etc.). You will not receive full credit if your queries
are all extremely simple.
Also write two each of the three types of SQL data modification
commands: insert, delete, update. Each of these should
be in a separate .sql file.
Use handin to submit a directory containing
- A text file containing your relational schema, with the
primary key identified in each relation.
- an updated copy of your E-R diagram from homework 5,
indicating any revisions you have made to it. (If there
are no revisions, just submit the same E-R diagram.)
- The .sql files used to create the database.
- The .sql files containing your queries and updates