Reference no: EM132281792
This assignment is centered on ER modelling, a graphical tool used in database design and Normalization which is a text based tool to remove unnecessary redundancy in a database. At the completion of these assessments students will be able to:
1. Identify the different components of an ERD
2. Recognize some business rules from the relationships contained in an ERD
3. Understand and use the 3 different type of relationship classifications 1:1, 1:M, M:N and their for the linking of tables in the RDM ((relational data model)
4. To develop relational models (RDM) where each table has a primary key (entity integrity) and some tables may have foreign keys (referential integrity)
Assessment Instructions
1. Convert M:N relationships into 1:M and M:1 relationships
2. Convert the conceptual ER model , given a problem scenario, into a RDM with appropriate primary keys (PKs) and foreign keys (FKs)
3. Only use Crow's Foot notation
4. Draw dependency diagram to use the 1NF, 2NF and 3NF
Projects Case Study
A construction company requires a database to record details about building projects. Each project has its own project number, name and employees assigned to it. Each employee has an employee number, name and job classification, such as engineer or computer technician.
The company charges its clients by billing the hours spent on each contract. The hourly billing rate is dependent upon the employee's position. For example, one hour of a computer technician's time is billed at a different rate than one hour of an engineer's time.
The first step is to examine the data provided in the report below, which contains the relevant information.
|
Proj No
|
Project Name
|
Emp No
|
Employee Name
|
Job Class
|
Charge/ Hour
|
Hours Billed
|
|
15
|
Evergreen
|
103
|
June Arbough
|
Electrical
Engineer
|
$84.50
|
23.8
|
|
|
|
101
|
John New
|
Database Designer
|
$105.00
|
19.4
|
|
|
|
105
|
Alice Johnson
|
Database Designer
|
$105.00
|
35.7
|
|
|
|
106
|
Bill Smithfield
|
Programmer
|
$37.75
|
12.6
|
|
|
|
102
|
David Senior
|
System Analyst
|
$96.75
|
23.8
|
|
18
|
Amber Wave
|
114
|
Annelise Jones
|
Application Designer
|
$48.10
|
24.6
|
|
|
|
118
|
James Frommer
|
General Support
|
$18.36
|
45.3
|
|
|
|
104
|
Anne Romares
|
System Analyst
|
$96.75
|
32.4
|
|
|
|
112
|
Darlene Smithson
|
System Designer
|
$45.95
|
44.0
|
|
22
|
Rolling Tide
|
105
|
Alice Johnson
|
Database Designer
|
$105.00
|
64.7
|
|
|
|
104
|
Anne Romares
|
System Analyst
|
$96.75
|
48.4
|
|
|
|
113
|
Dilbert Joenbrood
|
Application Designer
|
$48.10
|
23.6
|
|
|
|
111
|
Geoff Wabash
|
Clerical Support
|
$26.87
|
22.0
|
|
|
|
106
|
Bill Smithfield
|
Programmer
|
$37.75
|
12.8
|
|
25
|
Star Flight
|
107
|
Maria Alonzo
|
Programmer
|
$37.75
|
24.6
|
|
|
|
115
|
Travis Bawanyi
|
System Analyst
|
$96.75
|
45.8
|
|
|
|
101
|
John New *
|
Database Designer
|
$105.00
|
56.3
|
|
|
|
114
|
Annelise Jones
|
Application Designer
|
$48.10
|
33.1
|
|
|
|
108
|
Ralph Washington
|
System Analyst
|
$96.75
|
23.6
|
|
|
|
118
|
James Frommer
|
General Support
|
$18.36
|
30.5
|
|
|
|
112
|
Darlene Smithson
|
System Designer
|
$45.95
|
41.4
|
This represents the data as unnormalised and redundancy exists throughout the table. The operations (Business Rules) can be summarised as follows:
• The company manages many projects.
• Each project requires the services of many employees.
• An employee may be assigned to several different projects.
• Some employees are not assigned and perform duties not specifically related to a project. Some employees are part of a labour pool, to be shared by all project team. For example, the company's executive secretary would not be assigned to any one particular project.
• Each employee has a single primary job classification. This job classification determines the hourly billing rate.
• Many employees can have the same job classification. For example, the company employs more than one electrical engineer.
Tasks List
There are multiple tasks in the assessment
Task 1: Knowledge test
The knowledge test - Written computer-based assessment of Database Modelling and SQL concepts to work on the Assessment 1. The class test consists of short answers question about the basic concepts of Database modeling and Normalization.
Task 2 : Database Modelling and Implementation:
Question 1 - ER Modelling:
1. Business rules - Write Business rules to create ER Diagram. Write any assumptions
2. Entities and Attributes - List Entities, Attributes - Include all attributes that you believe would be useful
3. ER Diagram - Design an ER diagram for the above case study.
o Create your ER Diagram with Visio/Lucidchart/draw.io or another piece of software.
Hand-drawn diagrams will not be accepted
o Only use "crow's foot notation"
o ER Diagram should include Entities, attributes, relationships, connectivity and cardinalities.
o When completed, insert your ER Diagram into a Word document either by taking a screenshot of the diagram and pasting it in the word document, or by exporting the diagram as an image and the inserting it in the word document
Question 2 - Data Dictionary:
• Create a data dictionary for the above ER Diagram using the following sample format (Hint: Recreate a table in word using the same column headings).
• All data items must have suitable data types and all tables must have appropriate primary key and foreign key
|
Table
Name
|
Attribute
|
Attribute Description
|
Data Type
|
Data Format
|
Range
|
Mandatory
|
Primary Key/ Foreign Key
|
Foreign
Key Reference Table
|
|
|
|
Course
|
cID
|
Unique Identifier of Course
|
CHAR(4)
|
AA99
|
|
Y
|
PK
|
|
|
|
cName
|
Name of course
|
CHAR(10)
|
Aaaaaa
|
|
Y
|
|
|
|
|
cDesc
|
Description of Course
|
|
|
|
Y
|
|
|
|
|
dept
|
Name of department running the course
|
CHAR(2)
|
99
|
10-30
|
Y
|
FK
|
Dept
|
Question 3 - Normalization and Dependency Diagram:
• Briefly explain what is functional dependence and how does it relate to your solution?
• Briefly explain why would we use normalisation and how is it accomplished?
• Draw dependency diagram (using the following sample format) for your final ER Diagram, in the MS Word document, showing where necessary that you have progressed through to 3NF.
Question 4 - SQL Statements - DDL and DML:
Based on the specification you have provided in the data dictionary:
i. CREATE TABLE - Write the SQL code to create all tables (in the ER Diagram and Data dictionary) to implement the relational data logical model (as ONE script).
ii. Constraints - In part i, define primary keys, foreign keys and NOT NULL constraints in the CREATE TABLE statement ONLY. [Note: a foreign key constraint requires the existence of the referenced table].
iii. Write SQL statements to delete each table.
iv. Write INSERT INTO statements to populate each table. (at least 3 rows per table)