SQL + some diagrams

All instructions included in PDF also SQL must be done in Oracle ONLY

CS 450 Fall 2023 Project 1
Project Description
The goal of this assignment is to design a conceptual schema using an EER data model,
incorporating this schema into a RDBMS and running queries on this database.
Part 1: Design an EER data model (30 points)
Due: 9/15/2023 by 11:59 PM
Draw an EER diagram according to the notations discussed in class to accurately represent the
design specification below. You can use any tools (e.g., draw.io) to draw the EER diagram or
simply draw it by hand. In your diagram, indicate all the super classes, subclasses, constraints of
specialization/generalization, entity sets, relationship sets, cardinality ratios, participations,
attributes, and primary keys. Clearly specify any reasonable assumptions if they are not specified
in the design specification. Submit your EER diagram and assumptions in a PDF file.
Design Specification:
The following is a description of a database system that keeps track of various information on
campus including measurements obtained from sensors deployed inside different buildings.
These sensors monitor values such as temperature and light in the room (how illuminated the
room is). Your job is to design a database schema to store the data, and to provide specific
queries on the database.
The database must represent the following information:
Department Information
A department has a unique name, a web address, a phone number, an email address, and a
mailing address. The mailing address consists of street_address, city, state, and zip_code. The
individual components of the mailing address can be accessed separately, and the entire address
can also be retrieved as a unit.
Building Information
A building on campus has a unique name, a number of rooms, and a number of floors.
Room Information
Each room in every building has a number, an area (square feet), and one or more phone
numbers. The room number is unique for a given building, but a room in a different building
may have the same number. A room can be used as a lab, an office or a conference room. A
room can be used for more than one purpose. For example, a room can be a lab and an office.
Each conference room has a maximum capacity attribute. One building has several rooms while
each room is associated with only one building. There cannot exist any room with which no
building is associated.
Employee Information
Each employee has a unique id number, a name, a year of birth, and one or more email
addresses. Each employee works in one or more rooms. Each room is assigned 0 or more
employees. Each employee works for one or more departments. Each department has one or
more employees.
Measurement Information
A measurement record is a summarized record generated by sensor nodes deployed in every
room. Each measurement includes a date, a time, and numeric values for sound, temperature and
light of the corresponding room.
Part 2: Map the EER diagram from part 1 into relation schemas (20 points)
Due: 10/6/2023 by 11:59PM
Convert your EER diagram from part 1 into relation schemas. In a PDF file, specify the
followings for each relation:
(i) the name of the relation,
(ii) the names of its attributes,
(iii) the domain of each attribute,
(iv) the primary key, and
(v) the foreign key(s), if any.
If needed, you can update your EER diagram from part 1 before mapping. There is no required
format of your relation schemas. Submit your PDF file that has all the components listed
above for each relation and the EER diagram that your relation schemas are mapped from.
Part 3: Create a relational database based on part 2 and query the database (50 points)
Due: 10/27/2023 by 11:59 PM
Now, you are ready for implementation. Use appropriate naming conventions for all of your
tables and attributes. Write SQL commands to create tables and all other structures from part
2. For each table in your database, specify the primary key and indicate all reasonable foreign
key constraints, if any. Please populate the database with sufficient data to ensure your queries
will produce reasonable results.
Write the following queries in SQL and execute them on the database you created.
1. List the phone number, department name, and street address for each department.
2. Find the name of each building that has less than four floors.
3. Find the building name and the room number of each lab that is also used as office.
4. Count the number of rooms for each building.
5. Find the average temperature for each room.
6. Find the id of each employee who has exactly two email addresses.
7. Find the area of each room that hasn’t had any temperature recorded yet.
8. Find the phone number of the brightest room based on the daily average of the measured light
values.
Include all your SQL commands (create, insert, and query statements) in a single script file
(.sql). Please make sure that your script runs on sqlplus or SQL developer by connecting to the
Oracle server on-campus. Submit your script file and a log file showing the output of
running the script including the query results.
Some Tips:
1) At the beginning of your script, drop all tables that you are about to create. For example:
drop table products cascade constraints;
2) If you have trouble creating a particular table, try a different name. A name (like order,
group, user, etc.) may be a reserved word.
3) When creating tables that contain foreign keys, make sure the tables that are referenced
have already been created.
4) In order to run your script and capture the output of your script, you will need to use @
command and spool command in sqlplus. For example:
spool logname.txt
@scriptname.sql
5) In order to run your script on Zeus, you can transfer files to Zeus. For example:
scp scriptname.sql netid@zeus.vse.gmu.edu:.
Transfer files back to your local machine:
scp netid@zeus.vse.gmu.edu:filename .
More information: https://labs.vse.gmu.edu/index.php/FAQ/SSH
Instructions for connecting to Oracle
https://labs.vse.gmu.edu/index.php/Services/Oracle
Follow the link on the instructions page to activate your account to gain access.
Three ways to access Oracle on-campus:
1) Access Oracle on VSE LAB machines.
2) SSH to zeus.vse.gmu.edu by ssh netid@zeus.vse.gmu.edu
Run the command sqlplus once you are connected and enter your Oracle username and
password when prompted.
3) Install SQL Developer and make a connection.
More information: https://labs.vse.gmu.edu/index.php/Services/Oracle#sqldeveloper
Two ways to access Oracle off-campus:
1) Connect to VPN and then SSH to zeus.vse.gmu.edu by following the steps above.
2) Connect to VPN and then create a connection from SQL Developer GUI.