University of Buea (CEF 309/CEC 301)

Data Modelling Lab 1

sid grade
name addr cid title

Student take Course

M N
M M

advisor teach

1 1
Teacher

age
tid name

The objectives of this lab are:

1. Use a CASE tool (MySQL Workbench) to create the ER-diagram above

2. Investigate the difference between identifying and non-identifying relationships
3. Investigate the meaning of foreign keys
4. Forward-engineer the model into the relational model
5. Investigate primary and foreign key constraints

Step 1

a) Start MySQL Workbench

b) In the Modelling Section, select Create New EER Model
c) Change the default name of the physical schemata from mydb to model1
d) Double-click the Add Diagram button

Step 2: Create entity types

a) Use the Place New Table icon to place three tables on the drawing canvas
b) Double-click on each table on the canvas, and rename the tables to Student, Course, and Teacher,
respectively
c) Double-click on each table on the canvas, and define the table schemas

Nkweteyim (2013-2014)
 d) Save your model, and keep doing so as you complete the rest of the lab.

Step 3: Add relationships

The take relationship

a) Locate the n..m identifying relationship toolbar icon, and use it to create an n..m identifying relationship
between Student and Course.
b) Make a note of how MySQL Workbench handles this
a. A new entity type is created
b. Two 1..n relationships are created, one between Student and the new entity type, and the other
between Course and the new entity type
c. Observe that this new entity type is identified by the sid and cid keys from the Student and Course
entity types
d. sid and cid are foreign keys from the Student and Course entity types
e. They also serve as primary key (because we were creating an identifying relationship) in the new
entity type
f. Review your class notes on the conversion from the ER model to the relational model to verify that
the conversion of the n..m relationship to the relational model was done correctly
c) Do not forget to add the grade attribute to the newly created entity type above

The advisor and teach relationships

a) Add an 1..n non-identifying relationship between Student and Teacher

a. Recall from your class work that Teacher, being on the one side of the relationship, has to have its
key attribute as a foreign key in Student
b. Observe that the tid key is added to Student as a foreign key
c. Notice that because the relationship is not an identifying relationship, the tid foreign key does not
serve as part of the primary key in Student
d. This is in contrast to the identifying relationship (take), where the new entity type needed the
foreign keys from Student and Course to serve as primary key
b) In a similar way, add an M..1 non-identifying relationship between Course and Teacher

Your completed ER diagram

Nkweteyim (2013-2014)
Step 4: Forward engineer the model

a) Select the Forward Engineer option in the Database menu

b) Follow the steps in the wizard the comes up
c) Do not forget to give a name to your relational schema (e.g., mod1)

Step 5: View your relational model from the MySQL command line

a) Access the MySQL command line

a. Provide your password when requested to do so
b. Note: the command line utility was installed when you installed the MySQL DBMS, not when you
installed MySQL Workbench
c. The workbench relies on the DBMS to store its data
b) Type show databases; to see the currently available relational schemas
c) Verify that your forward-engineered
engineered relational schema is listed
d) Type use dbname to make your database active (replace dbname with the name of your database)
e) To access it, type show tables;
f) Verify that the entity types in your conceptual model were converted to the corresponding
corres relations

We shall return to the workbench to complete the exercises

Step 6: Populate your database with data

a) Select Edit Table Data option in the SQL Development section of Workbench
b) Follow the instructions in the wizard that comes up (use the mod1 schema)
c) Enter your data

Nkweteyim (2013-2014)
 Course Student Student_has_Course

Teacher

Verify how key constraints can help with data integrity by trying the following

a) In the Student relation, try to add a tuple with a sid that has already been used
b) In the Student relation, try to add a tuple without sid
c) In the Student relation, try to insert a tuple with a non-existent tid
d) In the student-has-course relation, try to insert an inexistent sid or cid
e) Try to delete a tuple in Student relation

Some Research to do

a) For each of the relations, select the Alter Table option (you can use this to modify the relation schema)
b) Investigate the options under the Column and Foreign key tab

Nkweteyim (2013-2014)