Cairo University
Faculty of Computers and Artificial Intelligence
Information Systems Department
Database I, Year 2022/ 2023
Lab - 7
(Entity Relationship Diagram (ERD) Power Designer and Physical Model)
For the last lab Case Study “University System”
1. Create the equivalent ERD using Power Designer tool
2. Then generate its physical model
3. Finally Generate the Database SQL script that will be implemented in SQL
Server Management Studio
Entity Relationship Diagram:
1
1. Steps for creating conceptual data model on Power Designer:
1. Open power designer
2. Create new conceptual data model
3. Add new entity [student entity]
2
4. Double click on added entity, then set entity information
5. Set student entity attributes: go to attributes tab then specify each attribute type
and name.
M ➔ mandatory attribute
P ➔ primary key
3
6. Press ok to save changes
7. Repeat the previous steps for all entities
4
8. Declare relationship between tables
Association Relation, Association link in toolbox
5
Final conceptual Data model
2. Steps for creating physical data model on Power Designer:
Open power designer.
1. Create the conceptual data model first.
2. Click on the Tools tab.
3. Choose “Generate Physical Data Model”.
At Wizard:
a. Generate new physical data model.
b. DBMS: Microsoft SQL server 2005.
c. Share DBMS definition.
d. Name: University_PhysicalModel.
4. Click on “Check Model” from same “Tools” tab.
6
The physical data model will be as follows:
7
3. Generate the script that will be implemented in SQL Server Management
Studio:
After generating the physical data model, click on Database tab then choose Generate
Database.
Generated SQL:
CREATE TABLE Professor
(
PID INT NOT NULL,
PName VARCHAR(25) NOT NULL,
Email VARCHAR(30) NOT NULL,
Address VARCHAR(50) NOT NULL,
PRIMARY KEY (PID)
);
CREATE TABLE Professor_TelephoneNumber
(
TelephoneNumber NUMERIC(11) NOT NULL,
PID INT NOT NULL,
PRIMARY KEY (TelephoneNumber, PID),
FOREIGN KEY (PID) REFERENCES Professor(PID)
);
CREATE TABLE Student
(
SID INT NOT NULL,
SName VARCHAR(30) NOT NULL,
GPA FLOAT NOT NULL,
PID INT NOT NULL,
PRIMARY KEY (SID),
FOREIGN KEY (PID) REFERENCES Professor(PID)
);
8
CREATE TABLE Course
(
CID INT NOT NULL,
CName VARCHAR(25) NOT NULL,
Description VARCHAR(50) NOT NULL,
PID INT NOT NULL,
PRIMARY KEY (CID),
FOREIGN KEY (PID) REFERENCES Professor(PID)
);
CREATE TABLE Department
(
DeptID INT NOT NULL,
DeptName VARCHAR(20) NOT NULL,
CID INT NOT NULL,
PRIMARY KEY (DeptID),
FOREIGN KEY (CID) REFERENCES Course(CID)
);
CREATE TABLE Enrolls-in
(
Year DATE NOT NULL,
Semester INT NOT NULL,
Grade INT NOT NULL,
SID INT NOT NULL,
CID INT NOT NULL,
PRIMARY KEY (SID, CID),
FOREIGN KEY (SID) REFERENCES Student(SID),
FOREIGN KEY (CID) REFERENCES Course(CID)
);
9
CREATE TABLE Requires
(
CID_1 INT NOT NULL,
RequiresCID_2 INT NOT NULL,
PRIMARY KEY (CID_1, RequiresCID_2),
FOREIGN KEY (CID_1) REFERENCES Course(CID),
FOREIGN KEY (RequiresCID_2) REFERENCES Course(CID)
);
Database Diagram in SQL
10
