Question

1. Write CREAT TABLE statements for the following tables (primary keys are underlined, foreign keys are in italic and bold). Make sure you have all needed constraints and appropriate datatypes for attributes:

Student (stID, stName, dateOfBirth, advID, majorName, GPA)

Advisor (advID, advName, specialty)

2.  Insert several records in each table.

Answer 1

Explanation:

The CREATE TABLE statement is used to create a new table.

Basic syntax of CREATE TABLE statement is as follows:

CREATE TABLE table_name
(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype
);

The unique name or identifier for the table follows the CREATE TABLE statement. Then in brackets comes the list defining each column in the table and what sort of data type it is.

Syntax for creating primary key is as follows:

CREATE TABLE table_name
(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY(one or more columns)
);

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.

If a table has a primary key defined on any field(s), then you can not have two records having the same value of that field(s).

Syntax for creating foreign key is as follows:

CREATE TABLE table_name
(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY(one or more columns),
FOREIGN KEY (column_name) REFERENCES tbl_name(column_name)
);

A foreign key is a key used to link two tables together. This is sometimes also called as a referencing key.

Now let us create Advisor table as :

CREATE TABLE Advisor(advID INT,advName VARCHAR(20) NOT NULL,specialty VARCHAR(20) NOT NULL,PRIMARY KEY(advID));

From the above create statement we can see that Advisor table is created with 3 columns:

Column name	Datatype	Constraint
advID	INT	PRIMARY KEY
advName	VARCHAR(20)	NOT NULL
specialty	VARCHAR(20)	NOT NULL

Now let us create Student table as :

CREATE TABLE Student(stID INT,stName VARCHAR(20) NOT NULL,dateOfBirth DATE NOT NULL,advID INT,majorName VARCHAR(20) NOT NULL,GPA INT NOT NULL,PRIMARY KEY(stID),FOREIGN KEY(advID) REFERENCES Advisor(advID));

From the above create statement we can see that Student table is created with 6 columns:

Column name	Datatype	Constraint
stID	INT	PRIMARY KEY
stName	VARCHAR(20)	NOT NULL
dateOfBirth	DATE	NOT NULL
advID	INT	FOREIGN KEY REFERENCES Student(advID)
majorName	VARCHAR(20)	NOT NULL
GPA	INT	NOT NULL

Now in order to insert values in the table.

The INSERT INTO statement is used to insert new records in a table.

It is possible to write the INSERT INTO statement in two ways.

The first way specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

If you are adding values for all the columns of the table, you do not need to specify the column names. The order of the values should be in the same order as the columns in the table.

The INSERT INTO syntax for second way would be as follows:

INSERT INTO table_name VALUES (value1, value2, value3, ...);

Following are the sample data :

INSERT INTO Advisor(advID,advName,specialty) VALUES(1,'Jones','ENG');  --- first way

INSERT INTO Advisor VALUES(2,'Thomas','MATH');  --- second way

INSERT INTO Student(stID,stName,dateOfBirth,advID,majorName,GPA) VALUES(1,'White','20-05-1962',1,'ENG',5);  --- first way

INSERT INTO Student VALUES(1,'Bell','10-08-1952',2,'MATH',7);  --- second way

we have inserted advisor details into Advisor Table and then student details in Student table which has the advisor ID from the Advisor table.