Question

Design an Access database including tables and queries for the model below

Relational Model (Project 9) Student SSN Name Address GPA Type SchoolName EnrollDate Current Student SSN ExpectedGraduate Alumni SSN GradDate EmployerName EmployerAddress Position Salary School Name Location Size OfLibrary BudgetForMBA AACSBStatus AACSBDate Curriculum Curriculum Code SchoolName SchoolName Course CourseCode Name Type Curriculum Code Course Topics CourseCode Topic Complete SSN CourseCode

Answer 1

Tables:

1.Student

create table Student(SSN varchar(10) primary key,Name varchar(40),Address varchar(60),GPA float(4,2),Type varchar(20),SchoolName varchar(40),EnrollDate date,foreign key(SchoolName) references School(Name));

2.CurrentStudent

create table CurrentStudent(SSN varchar(10),ExpectedGraduDate date,foreign key(SSN) references Student(SSN));

3.Alumni

create table Alumni(SSN varchar(10),GradDate sysdate(),EmployerName varchar(40),EmployerAddress varchar(60),Position varchar(15),Salary float(9,2),foreign key(SSN) references Student(SSN));

4.School

create table School(Name varchar(40) primary key,Location varchar(20),SizeofLibrary float(6,2),BudgetForMBA float(11,2),AACSBStatus varchar(15),AACSBDate date);

5.Curriculum

create table Curriculum(CurriculumCode varchar(5) primary key,SchoolName varchar(40) foreign key(SchoolName) references School(Name));

6.Course:

create table Course(CourseCode varchar(4) primary key,Name varchar(15),Type varchar(10),CurriculumCode varchar(5) foreign key(CurriculumCode) references Curriculum(CurriculumCode));

7.CourseTopics:

create table CourseTopics(CourseCode varchar(4),Topic varchar(15),foreign key(CourseCode) references Course(CourseCode));

8.Complete:

create table Complete(SSN varchar(10),CourseCode varchar(4),foreign key(SSN) references CourseStudent(SSN),foreign key(CourseCode) references Course(CourseCode));

Queries:

->select SSN from Student;

->select *from School;

->select CourseCode,CurriculumCode from Course;

->select *from Complete;

->select Name,BudgetforMBA from School;

Thus,above are tables and some queries.