Design a database and write CREATE TABLE SQL statements
Objective
The objective
Design a database and write CREATE TABLE SQL statements
Objective
The objective of this exercise is to learn how to design a database with additional relationships including more than one relationship between two entities, a unary relationship, and a time-dependent relationship.
To accomplish that objective, create a logical ERD using the crowsfoot format provided in the presentations and in the Modern Database Management text. Be sure each entity has the entity name at the top of the box, the primary key attribute or attributes in the middle of the box, and the non-key attributes in the bottom of the box. Lines should separate each part of the entity box.
Follow these instructions about each ERD:
●The ERD must not have any m:n relationships.
●All attributes must be placed within an entity.
●Each entity must have a primary key defined. A primary key may consist of one or more attributes.
●Each relationship is between two entities.
●Each relationship must include both a maximum and minimum cardinality for both sides of the relationship.
●Each relationship must have a foreign key. The foreign key is placed in only one of the two entities in the relationship. For a one-to-many relationship, the foreign key is placed in the entity that is on the many side of the relationship. For a one-to-one relationship, the foreign key can be placed in either entity, but is most often placed in the entity that you anticipate will have the fewest number of rows so it will have the fewest number of null values. Denote the foreign key(s) with the notation (FK) on the ERD.
●Do not differentiate between an identifying or non-identifying relationship – just use a solid line for each relationship.
Deliverables:
1) An ERD with relational crowfoots of the database model for points #1-11 below.
2) Type into a Word document the SQL CREATE TABLE statements necessary to create the Employee, SalesOffice, Property and EmployeeAssigned to a SalesOffice entities. There will be four CREATE TABLE statements required to satisfy this deliverable. Use data types you think are reasonable for the fields required to include in the CREATE TABLE statements. Be sure to include a primary key for each table and a foreign key for each relationship. A primary key should be either an INT or CHAR data type. A primary key should not be a VARCHAR data type.
Include the ERD in #1 above into that same Word document that has the CREATE TABLE statements.
Application Scenario:
A large real estate firm wants to keep track of its offices and employees, as well as the properties that are for sale through each office. Here is some information about the organization and data that is to be stored.
The firm has a number of sales offices in several states. Draw an entity for the sales office and include attributes of officeID (primary key) and address, city, state and ZIP Code. The firm only has offices in the U.S.
The firm has employees. Draw an entity for employee and include attributes of employeeID (primary key) and employee lastname and firstname. An employee is assigned to work in one and only one sales office; a sales office could have many employees assigned to it.
For each sales office, there is always one and only one employee assigned as the administrator for that office. An employee can administer only one office. Hint: It is possible to have more than one relationship between two entities. Each relationship must have its own relationship line and its own foreign key.
In the U.S., the 9-digit ZIP Code is used to determine a city and state, so create an entity for the location with the ZIP as the primary key, then relate that entity to the SalesOffice ZIP Code so the city and state are stored separately from the address in the SalesOffice entity.
The firm lists property for sale. Some of the attributes of property include propertyID (primary key), and address, city, state and ZIP. The firm lists properties only in the U.S., so the data for a city and a state should be consolidated into an entity. In the U.S., the 9-digit ZIP Code is used to determine a city and state, so create an entity for the location with the ZIP as the primary key, then relate that entity to the SalesOffice ZIP Code so the city and state are stored separately.
Each unit of property must be listed for sale with one and only one of the sales offices. A sales office may have any number of properties listed or may have no properties listed. For each unit of property, store the address and ZIP. A unit of property can have one location (city and state), but a location could be related to multiple units of property.
A unit of property is categorized by its type. For example, a unit of property may be a commercial building. Other category types are undeveloped residential land, single family residence, and multi-family residence. There are a number of pre-determined category types that are available, but a given unit of property can be of only one category type.
A unit of property is owned by one or more owners. An owner is uniquely identified by an ownerID and the attributes of an owner are lastname, firstname, and phonenumber.
It is possible an owner owns more than one unit of property. For each unit of property an owner owns, the real estate firm wants to keep track of the date of purchase and the percentage that the owner owns.
Some employees serve as managers of employees in the firm. Most employees have a manager. It is possible an employee does not have a manager. An employee can manage many employees, but an employee may not manage any employees. If an employee has a manager, then the employee has only one manager. A manager is always an employee of the firm. (Hint: this is an example of a unary relationship.)
It is possible an employee has worked for more than one sales office over time. We want to keep track of this time-dependent data. We want to keep track of all the offices that an employee has been assigned to, including the start and end date when the employee was assigned to work in a given sales office.