Data modeling

IS 441 Homework 2 - Data Modeling (Ch 2); Relational Model (Ch 4)

20 + 2 points (5, 6, 6, 5 respectively for the 4 questions)

Part 1: E-R Modeling

1. Create a database design for a college, modeling the relationships among DEPARTMENT (Use DEPT for entity name), FACULTY, COURSE, and STUDENT:

a. DEPT has at least four FACULTY members; each faculty member only works for one department;

b. DEPT has at least 10 COURSEs; a course only belongs to a department;

c. A faculty member can be assigned to teach up to four courses, but may not work in the current semester (“on sabbatical leave”); each course may be taught by at least one and up to three faculty members;

d. A course may be enrolled by at least 20, up to 40 STUDENTs; a student may be enrolled in not more than 5 courses, but may not be enrolled in the current semester.

In the E-R diagram, please include the following attributes for the entities involved:

DEPT: DeptName, Chair; COURSE: C-ID, Title; STUDENT: S-ID, SLN, SFN, Major

FACULTY: F-ID, FLN, Rank

2. Temporary Employment Corporation (TEC) places temporary workers in companies during peak periods. TEC's manager gives you the following description of the business:

e. TEC has a file of candidates who are willing to work.

f. Each CANDIDATE has several QUALIFICATIONs. Each qualification may be earned by more than one candidate. (For example, it is possible for more than one candidate to have earned a BBA degree or a Microsoft Network Certification. And clearly a candidate may have earned a BBA and a Microsoft Network Certification.)

g. TEC also has a list of COMPANYs that request temporaries.

h. Each time a COMPANY requests a temporary employee, TEC makes an entry in the OPENING folder. This folder contains an opening number, company name, required qualifications, starting date, anticipated ending date, and hourly pay.

i. Each opening requires only one specific or main qualification.

j. When a candidate matches the qualification, (s)he is given the job, and an entry is made in the PLACEMENT Record file. This file contains an opening number, candidate number, total hours worked, and so on.

k. TEC uses special codes to describe a candidate's qualifications for an opening. The list of codes includes:

Code Description

SEC-45 Secretarial work, at least 45 words per minute

SEC-60 Secretarial work, at least 60 words per minute

PRG-PY Programmer, Python

PRG-C# Programmer, C#

DBA-OR Database Administrator, ORACLE

DBA-DB2 Database Administrator, DB2

SYS-1 Systems Analyst, level 1

SYS-2 Systems Analyst, level 2

NW Network administrator

TEC's management wants to keep track of the following entities:

COMPANY

OPENING

QUALIFICATION

CANDIDATE

PLACEMENT

Part 2: Relational Modeling, Chapter 4

Present your work in BOTH “short text statement” – RELATION (Field1, Field2, Field3…)

AND “graphical representation” RELATION_NAME (w appropriate curvy arrows indicating referential integrity or “PK-FK pairs”).

3. Problem 4-46 of on P. 199. Ignore for now the last part regarding normalization (the last sentence).

[Remember: when do you have an incoming PK-FK referencing arrow, and when do you have an out-going one? Look at each relation (table) and ask: how many incoming arrows and how many out-going arrows this relation (table) has? Look at each relationship and ask: in this relationship, the arrow starts from which relation and ends at which other relation?]

4. Given the following E-R Diagram, please transform it to relational model, using BOTH short text statement AND graphical representation.

[Not all attributes are provided. Please complete the attributes on the relational model, based on your examination of the cardinalities as well as the types of entities. – Hint: How about foreign key?]

Plot ONLY the entities;

NO attributes.

Hint and precaution: do NOT confuse among these concepts – ENTITY, Attribute, and value of attribute

Hint: “TEC” is the background of the story; it is the USER of the database. Based on the hint, does TEC appear in the data model?

Feild1

Feild3

Feild2

See:

E

PK: Ke

Fe1

Fe2

Fe3

D

PK: Kd

Fd1

Fd2

B

PK:

Fb1

Fb2

C

PK: Kc

Fc1

Fc2

Fc3

A

PK: Ka

Fa1

Fa2

Fa3