1. Create the database, tables, and constraints to create a database for the mod
ID: 3866850 • Letter: 1
Question
1. Create the database, tables, and constraints to create a database for the model created in DMLab3.
2. Write sql statements to insert the data from the unnormalized student relation shown below into the database's tables. This will require entering at least one row into every table. Remember to insert into the parent table of any foreign key constraints before inserting into the child tables that reference it.
Number
Name
Major
Nickname
AdvisorNumber
AdvisorName
Club
ClubCost
1
James Smith
CIS
Jimmy
jsmith@pcc.edu
14
Jones
Sailing
25.00
1
James Smith
CIS
Jimmy
jsmith@pcc.edu
14
Jones
Drama
15.00
2
Barbara Bain
Physics
Babs
bbain@pcc.edu
15
Anderson
Drama
15.00
3
Phillip K Bob
English
Phil
pdick@pcc.edu
14
Jones
3
Phillip K Bob
English
Spook
pdick@pcc.edu
14
Jones
4
Dianne Duval
CIS
Dee
dduval@pcc.edu
15
Anderson
Sailing
25.00
5
Chester Arther
CIS
carther@pcc.edu
16
Fried
6
Jonathan Johnson
Mathematics
Jon
jjohnson@pcc.edu
15
Anderson
Chess
10.00
6
Jonathan Johnson
Mathematics
JJ
jjohnson@pcc.edu
15
Anderson
Chess
10.00
6
Jonathan Johnson
Mathematics
Jon
jjohnson@pcc.edu
15
Anderson
Sailing
25.00
6
Jonathon Johnson
Mathematics
JJ
jjohnson@pcc.edu
15
Anderson
Sailing
25.00
6
Jonathon Johnson
Mathematics
Jon
jjohnson@pcc.edu
15
Anderson
Drama
15.00
6
Jonathan Johnson
Mathematics
JJ
jjohnson@pcc.edu
15
Anderson
Drama
15.00
7
Mary Jensen
Chemistry
mjensen@pcc.edu
16
Fried
3. Write a single select statement with joins that reproduces the above tabe. Use the labels as column aliases. Don't use SELECT * for joined tables as it repeats the joined columns. Formatting is optional. For full credit, you must reproduce all information with a single SELECT.
Note that this assignment requires either SQL Server running locally on your computer (preferred) or using one of the scratch databases (e.g. cis275sandbox) on the school server. If you are running on the school server make sure to include some identifying information in the table name, e.g., YourName_STUDENT to avoid name clashes. Also remember that data is wiped from the scratch databases everyday at midnight, so make sure you save your scripts.
If you have any problems meeting the requirements of the previous paragraph contact me as soon as possible.
Let me know of any inconsistencies in the above data -- it's difficult to insert data consistently in non-normalized relations which is one of the reasons for normalizing in the first place!
Number
Name
Major
Nickname
AdvisorNumber
AdvisorName
Club
ClubCost
1
James Smith
CIS
Jimmy
jsmith@pcc.edu
14
Jones
Sailing
25.00
1
James Smith
CIS
Jimmy
jsmith@pcc.edu
14
Jones
Drama
15.00
2
Barbara Bain
Physics
Babs
bbain@pcc.edu
15
Anderson
Drama
15.00
3
Phillip K Bob
English
Phil
pdick@pcc.edu
14
Jones
3
Phillip K Bob
English
Spook
pdick@pcc.edu
14
Jones
4
Dianne Duval
CIS
Dee
dduval@pcc.edu
15
Anderson
Sailing
25.00
5
Chester Arther
CIS
carther@pcc.edu
16
Fried
6
Jonathan Johnson
Mathematics
Jon
jjohnson@pcc.edu
15
Anderson
Chess
10.00
6
Jonathan Johnson
Mathematics
JJ
jjohnson@pcc.edu
15
Anderson
Chess
10.00
6
Jonathan Johnson
Mathematics
Jon
jjohnson@pcc.edu
15
Anderson
Sailing
25.00
6
Jonathon Johnson
Mathematics
JJ
jjohnson@pcc.edu
15
Anderson
Sailing
25.00
6
Jonathon Johnson
Mathematics
Jon
jjohnson@pcc.edu
15
Anderson
Drama
15.00
6
Jonathan Johnson
Mathematics
JJ
jjohnson@pcc.edu
15
Anderson
Drama
15.00
7
Mary Jensen
Chemistry
mjensen@pcc.edu
16
Fried
Explanation / Answer
Given database table is that Student(Number,Name,Major,Nickname,Email,AdvisorNumber,AdvisorName,Club,ClubCost)
It is unnormalized database table. Because Advisor number,Advisor name are partially dependent on student table.
So first we need to normalize above relational table.
Normalisation is nothing but a technique that is used to organize data. It is a tecchnique of decomposing tables by eliminating data redundancy like Insertion, Update and Deletion Anamolies.
First Normal Form (1NF)
The above table satisfying 1NF.
Second Normal Form (2NF):
But here we are having partial dependancies.By eleminatiing then we can decompose tables as follows.
Student(Number,Name,Major,Nickname,Email,AdvisorNumber,Club,ClubCost)
Advisor(AdvisorNumber,AdvisorName,)
Inserting data into student table:
..................................
INSERT INTO Student VALUES (1,'James Smith','CIS','Jimmy','jsmith@pcc.edu',14,'Sailing',25.00);
INSERT INTO Student VALUES (1,'James Smith','CIS','Jimmy','jsmith@pcc.edu',14,,'Drama',15.00);
INSERT INTO Student VALUES (2,'Barbara Bain','Pysics','Babs','bbain@pcc.edu',15,,'Drama',15.00);
Inserting data into Advisor table:
........................................
INSERT INTO Student VALUES (14,'Jones');
INSERT INTO Student VALUES (15,'Anderson');
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.