Homework 5
Building a Database From Existing Data
This homework is worth 30 points.
Goals
When you have completed this homework you should have
- identified functional and multivalued dependencies.
- designed a database in BCNF and 4NF.
- built a multi-table database using the MySQL engine.
- used SQL commands such as CREATE TABLE and INSERT INTO.
- documented your design process.
Assignment
Starting from the data contained in ~dbennett/313/ch4/newcollege2010.csv design and implement a database in both BCNF and 4NF to contain this data. Your final
database should be implemented on CSCI313. I may look at your tables, so please do not submit your final report until these tables have been created.
Create a well formatted report which contains the following:
- A general discussion of the data.
- A general discussion of the database you have created.
- A list of all functional dependencies
- A list of all multivalued dependencies
- A list of any assumptions you have made.
- For each table created include
- A general description of the table.
- A justification for the creation of the table.
- Justification for the selection of the primary key.
- The schema for the table.
- A figure which contains the first few rows of the table.
- The script file which will
- Create the table
- Populate the table
- Include a section containing queries which will:
- Find the total of all students at Edinboro. This should search on the word "Edinboro". Future searches should work if the user changes the college name.
+---------------------------+----------------+
| UniversityName | Total Students |
+---------------------------+----------------+
| Edinboro University of PA | 8642 |
+---------------------------+----------------+
- Show the college name and the total male and female students for all Theological Seminaries. Include a total of all such students.
+----------------------------------+-----------+-------------+
| UniversityName | Total Men | Total Women |
+----------------------------------+-----------+-------------+
| Biblical Theological Seminary | 184 | 64 |
| Byzantine Catholic Seminary | 13 | 1 |
| Calvary Baptist Theo Seminary | 98 | 8 |
| Evangelical Theological Seminary | 94 | 59 |
...
| Trinity School for Ministry | 102 | 67 |
| Westminster Theological Sem | 479 | 135 |
| NULL | 1719 | 825 |
+----------------------------------+-----------+-------------+
- Find all of the Counties which do not have colleges with graduate students.
+--------------------------------+
| Counties without Grad Programs |
+--------------------------------+
| Crawford |
| Huntingdon |
The report is worth 14 points, the final tables are worth 10 points and the queries are worth 6 points.
This report should be printed and turned in at the beginning of class.