A computer science department staff employee has a unique account name, first name, last name, titles,

and phone extensions. First and last names are non$-$null.

Computer science department staff can only be assigned to a single location that is an office. An office

may have many computer science department staff. Consider the type of relationship between offices

and locations that would permit this constraint to exist. An office only exists at a single location.

A hallway is a type of location and has a long name $($description$)$ associated with it$.$

A title has a unique acronym and a unique name. The title acronyms are used much more often in our

database system than the title names.

An edge connects two adjacent locations with one another from a starting location to an ending location.

Note that since it is directed, you will need to include both directions. In order words, you need to have

an edge with $(3H1,3H2)$ and an edge with $(3H2,3H1).$ Do not worry about the other floors. Edges should

be unique since no duplications should exist.

A path consists of an ordered list of locations from the starting location to the final location.

Requirements:

Design an ERD that captures the above requirements. Follow the notations given in the course slides, and

also follow the given guidelines for Good Design.

State any assumptions that you make in addition to the above requirements.

Translate your ERD into a relational model for the above application. You need to follow the rules that

convert the ERD to relational model.

A copy of the CS Fuller Directory from a past year, and the Fuller $3^{rd}$ floor map with locations and edges is

provided to you for data entry. Note that the directory is NOT representative of what your tables should

look like! You will need to modify the tables in the spreadsheet to match your relational schema before

you create the tables and populate them with data.

Write the SQL code in a file named $p1.sq$ to

a$.$ Create the tables including the constraints.

b$.$ Enter the directory and floor plan data for the third floor.

c$.$ Enter data for just the following $3$ paths:

i$.$ elevator E$1$ to room $320$

ii$.$ room $312$ to room $319$

iii. bottom stairs $$2$ to room $308$Description:

In this phase you will be designing the ERD, the relational schema, and writing the SQL using Oracle SQL Developer

or a similar IDE to create the database that software engineers will use to create a Fuller Labs application to

manage the directory and create paths to destinations on the third floor. For this assignment you will implement

the information for the entire directory as given in the accompanying Fuller Directory spreadsheet, and the

directed path for just the third floor $($see attached image$).$ You will implement the directed graph from the third

floor image containing blue dots $($locations$)$ and the red lines connecting them $($edges$).$ You will need to

determine a logical naming scheme for hallway, elevator and staircase locations on the map. Write down the map

coordinates for each of the dots.

The naming scheme $($important for grading purposes$)$ for the primary key constraints is

Tablename$\_$attributename$($s$)\_$PK

The naming scheme for the foreign key constraints is

Tablename$\_$attributename$($s$)\_$FK

If fields are unspecified variable length characters, use varchar$2(40)$ as the SQL data type. For unspecified ID fields

or number fields, use number$(5).$

Do not use the ALTER TABLE statement to create the primary and foreign key constraints.

If you use inheritance in your design, apply the ISA Method A$2($see class notes PersonA$2$ from week $4)$ when

translating the ERD into a relational schema.

A copy of the CS Fuller Directory from many years ago, and the Fuller $3^{rd}$ floor map with locations and edges is

provided for your data entry. Note that the directory is NOT representative of what your tables should look like!

Make sure to create your conceptual ERD and then translate it to a relational schema. You will need to modify the

tables in the spreadsheet to match your relational schema before you create the tables and populate them with

data.

The database includes the following:

A location has a unique id$,$ a location name, a location type, $x$ and $y$ coordinates on a map image, and the

floor it is on in Fuller Labs. The combination of $x-$coordinate, $y-$coordinate, and the floor is also unique.

The possible values for the floors are $1,2,3,A,$ and $B.B$ is the basement, and $A$ is the sub$-$basement.