SQL

CIS 3650 Group Project

Maximum points possible: 100

Instructions:

a. This is a group project. Information on groups formed by you is posted under Course

Information on Blackboard. If you are not part of any group, if will be your responsibility

to bring it to the instructor’s notice. Not being part of any group will result in a 0 for the

project.

b. Your answers must be typed using any word processing software, for answers to the

questions.

c. You can do the design/diagrams using any software such as ERWin/ Vizio etc. If you do

so, please include your diagrams you created as PDF files. There is no requirement that

you must use a diagramming/design software. You can also draw these diagrams in MS

Word. Please do NOT submit handwritten diagrams (grade = 0).

d. ONE submission per group is sufficient. Group members not attaching the file must type

in the name of the group member who would be uploading the document on Blackboard

as their submission.

e. All Submissions must be made online under Assignments by the due date mentioned

above. Do not email your deliverables to me. I am unable to grade emailed deliverables.

f. The deliverable for the project are: (1) Normalized tables showing the data in the new

tables, with primary and foreign keys identified and assigned for each table. (2) ER

diagram for the database with relationships and minimum and maximum cardinalities

marked. Your ER diagram should spell out descriptions for the relationships between the

tables in both directions, and cardinalities, use appropriate notation for the crow’s foot

diagram, and designate identifiers for each relation. (3) SQL script for creating the

database (DDL statements for creating the tables). (4) Data dictionary with each column

and its data type and all related details.

A new, startup IT organization is entering the growth phase. Last month their organization size

grew from five employees to more than twenty due to several new projects begun. The details of

employees and their project assignments are shown in the figure. Based on the information

provided,

Section – I (30 Points)

a. Identify and list all functional dependencies (both partial dependencies and transitive

dependencies) and provide your justifications or assumptions for the dependencies

identified.

b. Examine the data provided. Identify and list any multivalued dependencies that you may

find. How many multivalued dependencies are present? Provide your justifications or

assumptions for the dependencies identified.

c. List all candidate keys

d. Based on your answers in a), b) and c), cast the data provided to 1NF.

e. Based on your answers in d), normalize the relations to 2NF. Provide justification/s or

assumptions for each step in your normalization process.

f. Based on your answers in e), normalize the relations to 3NF. Provide justification/s or

assumptions for each step in your normalization process.

g. Are the relations in your answer in f) in Boyce Codd Normal Form (BCNF)?

h. If not in BCNF, Normalize the relations in f) to BCNF. Provide justification/s or

assumptions for each step in your normalization process.

i. List all primary keys and foreign keys

Section – II (25 Points)

a. Based on your normalization in section-I, draw the entity relationship diagram using the

Crow’s foot notation.

b. Identify and include any cardinalities that you believe exist, based on the information

provided.

c. Does your database design exhibit database integrity? Justify your answer/s.

Section – III (25 Points)

Create the tables you identified in Normalization/ER Diagram. Provide the SQL syntax for Data

Definition Language (DDL to create the database you have designed. Include the MsSQLServer

database for the database you designed as a .sql file. (In otherwords, save your DDL statements

(sql to create tables and insert data) and submit it as a deliverable).

Section – IV (20 Points)

Create a Data Dictionary for the project.

Please make sure your tables that you have in your normalization in section I and all other table details match up with and are exactly the same as the relations and their relationships you show in Section II (ER diagram). Make sure all of the above matches with the tables you created in your Section III (DDL statements/SQL).

PROJECT NUMBER

PROJECT NAME

EMPLOYEE NUMBER

EMPLOYEE NAME

JOB CLASS CHARGE PER HOUR

HOURS BILLED

TOTAL CHARGE

15 Evergreen

103 June E. Arbough Elec. Engineer $84.50 23.8 $2,011.10

101 John G. News Database Designer

$105.00 19.4 $2,037.00

105 Alice K. Johnson * Database Designer

$105.00 35.7 $3,748.50

106 William Smithfield Programmer $35.75 12.6 $450.45 102 David H. Senior Systems Analyst $96.75 23.8 $2,302.65

Subtotal $10,549.70

18 Amber Wave

114 Annelise Jones Applications Designer

$48.00 24.6 $1,183.26

118 James J. Frommer General Support $18.36 45.3 $831.71 104 Anne K. Ramoras * Systems Analyst $96.75 32.4 $3,134.70

112 Darlene M. Smithson

DSS Analyst $45.95 44 $2,021.80

Subtotal $7,171.47

22 Rolling Tide

105 Alice K. Johnson Database Designer

$105.00 64.7 $6,793.50

104 Anne K. Ramoras Systems Analyst $96.75 48.4 $4,672.80

113 Delbert K. Joenbrood *

Applications Designer

$48.00 23.6 $1,135.16

111 Geoff B. Wabash Clerical Support $26.87 22 $591.14 106 William Smithfield Programmer $35.75 12.8 $457.60

Subtotal $13,660.10

25 Starflight

107 Maria D. Alonzo Programmer $35.75 24.6 $879.45 115 Travis B. Bawangi Systems Analyst $96.75 45.8 $4,431.15

101 John G. News * Database Designer

$105.00 56.3 $5,911.50

114 Annelise Jones Applications Designer

$48.00 33.1 $1,592.11

108 Ralph B. Washington

Systems Analyst $96.75 23.6 $2,283.30

118 James J. Frommer General Support $18.36 30.5 $559.98

112 Darlene M. Smithson

DSS Analyst $45.95 41.4 $1,902.33

Subtotal $17,559.82