7 short labs in Database

GEIT 3341 DATABASE I LAB 8

GEIT 3341 Database I

Lab 8

Database Design using ERDPlus

Due Date:

Objective(s) Target CLO(s) Reference

To practice Entity Relationship (ER)

modeling of Chapter 12 by using a

tool called ERDPlus to

1) Graphically create E-R diagrams

based on business rules.

AND

2) Generate the database by

automatically generating

complete DDL scripts.

1 Instructor Demo

Chapter 12

ID Name Section

GEIT 3341 DATABASE I LAB 8

Instructions:

Use the ERDPlus tool to create an ER diagram to support the following

requirements for a particular company:

– The company is organized into DEPARTMENTs. Each department has a

name, number and an employee who manages the department. We keep

track of the start date of the department manager.

– Each department controls a number of PROJECTs. Each project has a name,

number and is located at a single location.

– We store each EMPLOYEE’s first name, middle initial, last name, social security

number, address, salary, sex, and birthdate. Each employee works for one

department but may work on several projects. We keep track of the number of

hours per week that an employee currently works on each project. We also keep

track of the direct supervisor of each employee.

– Each employee may have a number of DEPENDENTs. For each dependent, we

keep track of their name, sex, birthdate, and relationship to employee.

Use the following table to set the data types:

Attribute Name Data Type

Name VARCHAR(20)

Ssn CHAR(9)

DOB DATE

Salary NUMERIC(6,2) where 6 is the Length and 2 is the Precision

Pname VARCHAR(20)

Pnumber INTEGER

Plocation VARCHAR(20)

Dependent_name VARCHAR(20)

Sex CHAR(1)

Relationship VARCHAR(20)

Dname VARCHAR(20)

Dnumber INTEGER

Hours NUMERIC(3,1) where 3 is the Length and 1 is the Precision

GEIT 3341 DATABASE I LAB 8

Your diagram should look like the one shown in Figure 1 below:

Figure 1: ER Diagram

What you need to hand in:

1. Using the Export Image… option of MENU (see Figure 2), generate an image for the diagram

(which will be in a PNG format) and hand in this image. (8 points) 2. Using the Generate SQL option (see Figure 3), generate the DDL, click on the Copy button, copy

this script into a Notepad/Word file and hand in this file. (2 points)

GEIT 3341 DATABASE I LAB 8

Figure 2: Export Image Option

GEIT 3341 DATABASE I LAB 8

Figure 3: Generate SQL Option