Lab # 07
Database management system
Fall 2021
Instructor
Student Name
CMSID
Department
Semester
Lesson Set Introduction to Primary key and foreign key,
create diagram from the database
7
Purpose 1. To get a basic awareness primary key
2. To understand the usage of primary key
3. How and where we can use foreign key
4. Create Diagram from the existing database
Procedure 1. Students should read the Pre-lab Reading assignment before coming to the
lab.
2. Students should complete the Pre-lab Writing assignment before coming to
the lab.
3. Students should complete Labs 7.1 through 7.2 in sequence in the lab. Your
instructor will give further instructions on grading and completing the lab.
4. Students should complete the set of lab tasks before the next lab and get
them checked by their lab instructor.
Contents Pre-requisites Completion Page
Time Number
Pre-lab Reading Assignment - 20 min 3
Pre-lab Writing Assignment Pre-lab Reading 10 min 6
Lab 7
Lab 7.1 Pre-lab reading 30 min 6
Recalling the SQL syntax
Lab 7.2 Awareness of - 7
Lab Tasks Syntax Rules
2|Page
PRE-LAB READING ASSIGNMENT
Primary key
A primary key is a field in a table which uniquely identifies each row/record in a database
table. Primary keys must contain unique values. A primary key column cannot have
NULL values.
A table can have only one primary key, which may consist of single or multiple fields.
When multiple fields are used as a primary key, they are called a composite key.
If a table has a primary key defined on any field(s), then you cannot have two records
having the same value of that field(s).
Note − You would use these concepts while creating database tables.
Create Primary Key
Here is the syntax to define the ID attribute as a primary key in a CUSTOMERS table.
CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
To create a PRIMARY KEY constraint on the "ID" column when the CUSTOMERS table
already exists, use the following SQL syntax −
ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID);
Foreign key
A foreign key is a key used to link two tables together. This is sometimes also called as a
referencing key. A Foreign Key is a column or a combination of columns whose values
match a Primary Key in a different table.
The relationship between 2 tables matches the Primary Key in one of the tables
with a Foreign Key in the second table.
If a table has a primary key defined on any field(s), then you cannot have two records
having the same value of that field(s).
Example
Consider the structure of the following two tables.
CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
3|Page
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
CREATE TABLE ORDERS (
ID INT NOT NULL,
DATE DATETIME,
CUSTOMER_ID INT references CUSTOMERS(ID),
AMOUNT double,
PRIMARY KEY (ID)
);
If the ORDERS table has already been created and the foreign key has not yet been set,
the use the syntax for specifying a foreign key by altering a table.
ALTER TABLE ORDERS ADD FOREIGN KEY (Customer_ID) REFERENCES
CUSTOMERS (ID);
DROP a FOREIGN KEY Constraint
To drop a FOREIGN KEY constraint, use the following SQL syntax.
ALTER TABLE ORDERS DROP FOREIGN KEY;
Database Diagram
It is helpful to have a visual representation when designing a database. MySQL
Workbench allows you to create entity relationship diagrams (ERDs) with relationships
between the tables (one to one, one to many) and rearrange them as needed. Below we
cover how to create an ERD from a downloaded SQL file.
Step1: open your workbench application and navigate to the database menu.
Step2: click the reverse engineer and you will get new window and just click the next
button below.
Step3: when you click the next button you will get popup window for the password, to
verify that you are the owner of this server. Just type password and click ok.
4|Page
Step4: when you click ok in password popup window you will get new window to select
the database that you want to view the diagram of that database just select database
and click next>next>password and execute to finish and view the diagram.
5|Page
Pre-lab writing assignment
Fill in the blanks 1. The relationship between 2 tables matches the ________ in one
of the tables with __________ in the second table
2. It is helpful to have a __________ when designing a database
3. A foreign key is a key used to link ________ together.
4. A primary key column cannot have ________ values.
6|Page
Lab 7.2 Lab Tasks
1. Create a new table with the named City and add two columns, city_id and city_name and make
the city_id column as primary key and auto_increment.
2. Add at least 5 records in city table.
3. Alter the LAB_6 student table and make the std_id column as primary key and the city column as
foreign key.
NOTE: You can take guide from the pre_reading section that how you can make diagram.
4. Create diagram from the existing database and paste and export as PNG and paste in the given
box below
7|Page
