Microsoft Acces Database (Assignment) Develop a Student Grade Database consistin
ID: 3914664 • Letter: M
Question
Microsoft Acces Database (Assignment)
Develop a Student Grade Database consisting of the following two tables;
STUDENT
Z number (PK) (Number Field) contains nine digits
Student Name (Text field -25)
College (Text field -3) FAU
Email address (Text field-15)
GRADE
Grade ID auto number
Course ID (Number Field) contains five digits
Course Name (Text-8)
Z number (FK) (Number Field) contains nine digits
Grade (Number) contains two digits
Enter 5 students into the database, each having taken at least three of the following four courses ( ISM 3011, ISM 3116,ISM 4133, ISM 4323). Make up five unique Z numbers for each student. Make up a five digit Course ID for each course. Enter numeric grades for each course taken
Establish a relationship between STUDENT and GRADE using the Z number . Check for referential integrity
Generate the following queries:
1. For each course, the lowest, highest and average grade(no decimals)---------COURSE STATS
2. Average grade for each student for all courses taken--------------STUDENT GRADE AVERAGE
3. Student List -----STUDENTS
Save and upload file
Explanation / Answer
Answer)
SQL Statements for create table:
CREATE TABLE STUDENT(
Znumber int(9) primary key,
StudentName varchar(25),
College varchar(3),
EmailAddress varchar(15)
);
create table GRADE(
GradeID AUTO_INCREMENT,
CourseID int(5),
CourseName varchar(8),
Znumber int(9),
Grade int(2),
FOREIGN KEY (Znumber) REFERENCES STUDENT(Znumber)
);
Insert statements for inserting data;
insert into STUDENT values(1,'Name1','College1','email1@address.com');
insert into STUDENT values(2,'Name2','College2','email2@address.com');
insert into STUDENT values(3,'Name3','College3','email3@address.com');
insert into STUDENT values(4,'Name4','College4','email4@address.com');
insert into STUDENT values(5,'Name5','College5','email5@address.com');
insert into GRADE(CourseID,CourseName,Znumber,Grade) values(12345,'ISM 3011',1,85);
Q1. For each course, the lowest, highest and average grade(no decimals)::
select min(Grade) as MIN, max(Grade) as MAX, round(AVG(Grade),0) AS AVG_GRADE from GRADE;
Q2) Average grade for each student for all courses taken::
select Student.StudentName,avg(GRADE.grade) from Student inner join Grade on Student.Znumber=Grade.Znumber group by Student.Znumber;
Q3. Student List::
select * from Student;