Part 1
Suppose that you are required to develop a simple database for a design firm using the following instructions:
§ The database can be used by multiple projects. A project is composed of design items.
§ The organization has several departments: Architectural Department, Structural Department, Environmental Department, and Land Development Department etc. One or more departments may participate in one project. Any department can participate in several projects.
§ A department may employ many employees, but each employee may be employed by one department.
§ Each department is managed by only one of the employees.
§ The organization has a training program in which an employee may manage (or train) many other employees and each employee is managed (or trained) by one employee.
§ During the implementation of a project, project documents (reports or drawings) are generated.
§ One document is produced by one or many employees and an employee can produce several documents. A design item consists of multiple documents.
§ The developed database should provide the users with name of the project, its start-date, its end-date, contract price, design fee of each design item, involved departments, basic information of involved employees, the number of employees in the organization, the number of Licensed Professional Engineers etc.
Develop an Entity-Relationship diagram based on the instructions above. Please explicitly indicate any assumptions you are making while building this E-R diagram.
Part 2
Develop relation schemas from the entity sets and relationships developed for part 1 and fill the relations with some envisioned (but somewhat reasonable) examples. (2-3 examples max.)
Part 3
Normalize this dependency diagram to 3NF.
Part 4
- Break up the dependency diagram below to create 2 new dependency diagrams, one in 3NF and one in 2NF.
- Modify the dependency diagrams you produced in 4.1. to produce a set of dependency diagrams that are all in 3NF. To keep the entire collection of attributes together, copy the 3NF dependency diagram from 4.1 and then show the new dependency diagrams that are also in 3NF. (Hint: One of your dependency diagrams will be in 3NF, but not in BCNF).
Part 5
Remember the requirements of 1NF, 2NF and 3NF and answer the following.
- Is the table below in 1NF? Explain your answer.
- Normalize the table in 5.1. to 2NF and show the resulting table(s).
- Normalize the ‘Courses’ table below to 3NF.
Assume the following:
· Venue depends on course code only.
· One course code can be taught by different instructors from different departments.
· Different professors may have the same name.
In this assignment, you will code solutions to the four SQL problems described below. To run SQL code, you can use one of the ways mentioned below. What you need to submit are text files with the SQL commands that you come up with, one file for each question (Q1.sql, Q2.sql, Q3.sql, Q4.sql). PLEASE MENTION AT THE TOP OF EACH FILE, which database (e.g., Oracle, SQLite) you used for that question! I will execute the SQL commands from the text files you submit, using the same software you used, to see if they produce the expected results. You can also submit a README text file containing anything else you want to communicate to me, regarding your queries etc. Please reach out to me if you need one-on-one help.
Ways to ‘do’ SQL:
SQL is a data creation + manipulation language, so it’s best learned HANDS-ON (not just by looking at slides and reading about the syntax) – you need access to a relational database where you can create tables, enter data in them and do queries on the data (tables ← data ← queries).
There are different ways to get your hands on a DB: use a browser page [a server runs the DB software, you simply access it from a page]; install a DB locally on your laptop/tablet/phone; use a ‘cloud-based’ DB [this is similar to, but more powerful than, accessing a DB via a web page].
An easy way to practice running SQL queries is to use browser-based interfaces (nothing to download, no login needed) to create/query databases. Here are some sites that provide this form of access:
- SQL Tutorial: http://www.w3schools.com/sql/default.asp
- ideone: http://www.ideone.com
- sqlfiddle: http://sqlfiddle.com/
- Code School’s Try SQL: http://campus.codeschool.com/courses/try-sql/contents
- SQLZOO: http://sqlzoo.net/ – has extensive tutorials
- Another tutorial: http://www.sqltutorial.org/
- w3resource: http://www.w3resource.com/sql-exercises/ – more tutorials
- Khan Academy: https://www.khanacademy.org/computer-programming/new/sql
- ‘Online SQL interpreter’: https://sql-js.github.io/sql.js/examples/GUI/ -JavaScript-based!
Question 1 (40%) Write SQL commands to create the table below (create your own column names) and populate them with data (as many/few rows as you see fit (e.g., 25 employees, 15 meetings) you can start small, and add more rows later, to help write queries for Q2-Q4).
Question 2 (20%) Write a query to output the most-self-reported symptom.
Question 3 (20%) Write a query to output the ‘sickest’ floor.
Question 4 (20%) The management would like stats, for a given period (between start, end dates), on the following: number of scans, number of tests, number of employees who self-reported symptoms, number of positive cases. Write queries to output these.
