Transcript Student relational database system

Relational Database in Access
Student System
As always please use speaker notes!
Student relational
database system
We are going to design tables for a system to contain student
information. Think of this as the type of information that would
be needed for a transcript.
We need:
Information about the student: idno, name and address,
major etc.
Information about the courses the students have taken
including grade
Tables that will allow us to get the name of the major and
the name of the course
Student relational
database system
STUDENT TABLE:
This table has information
about the student. All of it
must be dependent on the
primary key.
Student Idno - this is the primary key since all information relates to it
Name
Address
Phone
Social Security # - this is a candidate key since it could be used as a primary key
Major code
Date enrolled
Clearly we need major
Other information related directly to the student
information about the major as
well, but that cannot be stored
on the student table because the
name of the major and the chair
of the department directly relate
to the major code, not to the
MAJOR TABLE:
student idno. Therefore we
need a major table.
Major code - this is the primary key
Major name
Chair department
Student relational
database system
Information about the courses the student has taken can not be kept on
the STUDENT TABLE because they are a reoccurring group and thus
break the rules of normalization. Essentially if we were to attempt to
carry course information on the STUDENT TABLE we would have to
determine how many slots we need (the maximum number of courses
a student would be allowed to take). This is not practical and it
definitely breaks the first normal form rule.
STUDENT COURSE TABLE:
Student Idno
Course code
Semester course taken
Grade
COURSE TABLE:
Course code - Primary key
Course name
Number credits
Primary key
The primary key has to be made up of more than
one column/field because each student is
allowed to take more than one course. The
combination of student idno and course code
and semester course was taken means that we
will have a separate record for each time a
course was taken by a student.
We cannot keep the course name in the STUDENT COURSE
TABLE because the course name directly relates to the course
code. This breaks normalization rules.
Practically speaking, we would not want to carry the course name
in the STUDENT COURSE TABLE because if the course name
changes we have to change it on any record. By carrying it on a
separate COURSE TABLE, if the name changes we only have one
place to enter that change.
Create tables
Create tables
SQL
SELECT student00.studentidno, student00.name, student00.majorcode, major00.majorname
FROM student00 INNER JOIN major00 ON student00.majorcode = major00.majorcode;
This is the SQL code that Access generated. Notice that each field name has the table name
in front of it.
The FROM clause does an INNER JOIN which links the student00 table and the major00
table based on the majorcode on student00 being equal to the majorcode on major00.
SELECT studentidno, name, student00.majorcode, majorname
FROM student00, major00
WHERE student00.majorcode = major00.majorcode;
This is alternate SQL code that I wrote. I only used the table name with majorcode because
majorcode is the only column in the SELECT that appears in both tables.
The FROM clause lists the tables involved in the query.
The WHERE clause establishes the link or relationship between the two tables based on the
majorcode in student00 and the majorcode in major00.
Relate 3 tables
SQL
SELECT stucourse00.studentidno, student00.name,
stucourse00.coursecd, course00.coursename, stucourse00.grade
FROM (student00 INNER JOIN stucourse00 ON
student00.studentidno = stucourse00.studentidno) INNER JOIN
course00 ON stucourse00.coursecd = course00.coursecd;
SQL
SELECT stucourse00.studentidno, student00.name,
stucourse00.coursecd, course00.coursename, stucourse00.grade
FROM (student00 INNER JOIN stucourse00 ON
student00.studentidno = stucourse00.studentidno) INNER JOIN
course00 ON stucourse00.coursecd = course00.coursecd;
This is the SQL generated by Access:
The SELECT is selecting the columns with the table name in front of each column.
In the FROM the relationships are set using the INNER JOIN. This says that student00 and stucourse00
are joined based on the studentidno in each of the tables and then the join is to course00 based on the
coursecd in stucourse00 and course00.
SELECT stucourse00.studentidno,name, stucourse00.coursecd,
coursename, grade
FROM stucourse00, student00, course00
WHERE stucourse00.studentidno = student00.studentidno AND
stucourse00.coursecd = course00.coursecd;
This is alternate SQL code that I wrote:
The SELECT is only using table names when column names are not unique.
The FROM lists all tables being used in the query.
The WHERE shows the relationship between the tables by saying that the studentidno on stucourse00
and student00 must be equal AND the coursecd on stucourse00 and course00 must be equal.
SQL
SELECT stucourse00.studentidno, student00.name, student00.majorcode,
major00.majorname,stucourse00.coursecd,course00.coursename, stucourse00.grade
FROM ((stucourse00 INNER JOIN student00 ON stucourse00.studentidno =
student00.studentidno) INNER JOIN course00 ON stucourse00.coursecd =
course00.coursecd) INNER JOIN major00 ON student00.majorcode = major00.majorcode;
The SQL that Access generates uses the INNER JOIN to establish the links between the four
tables. Note the structure of the INNER JOIN in the example above.
SELECT stucourse00.studentidno, name, student00.majorcode, majorname,
stucourse00.coursecd,coursename, grade
FROM stucourse00, student00, major00, course00
WHERE stucourse00.studentidno = student00.studentidno AND stucourse00.coursecd =
course00.coursecd AND student00.majorcode = major00.majorcode;
The alternate SQL that I coded lists all of the tables in the FROM and then establishes the links
through the WHERE.