Q2. Homework Assignment Website. The Somerdon High School PTO is creating a webs

Question

Q2. Homework Assignment Website. The Somerdon High School PTO is creating a website that will allow students to quickly look up their homework assignments.

Functional Requirements:

The database supporting this website will keep track of departments, teachers, classes, and assignments. Each department has a name such as "math", "science", or "English", and each teacher is a member of a single department.

For each teacher, the database should store a title ("Mr.", "Ms.", "Dr." etc.), first name, middle initial, last name, office phone number, and e-mail address.

Each class is taught by one teacher; for each class, the database should store a name, description, meeting period (for example, 7th period), and room number. Finally each class has multiple assignments.

For each assignment, the database should store the date assigned, the date due, and a text description of the work assigned.

Obviously, the database also needs to remember which class each assignment is for, and which teacher teaches each class.

Design a database to hold all this information. Hand in a database design outline and an ER diagram. Additionally create all the necessary tables for this database, and choose a reasonable data type for each attribute. Create all necessary relationships between the tables and enforce referential integrity. You do not have to enter any data or create any forms.

Delivery Requirements:

For the database design I expect the following:

A one to two paragraph description of the database you are designing.

A listing of all the tables required to design the database.

A listing of fields, sorted by table, for each table.

With each field, you must list the data type for the field.

Each table must have a defined primary key.

Please provide a bulleted list of all relationships.

In addition to the table information, you must create a high level ERD diagram showing the tables within Visio.

When the Visio diagram is complete, cut and paste the diagram into Microsoft Word and name the document after your last name and Question 1 ( ie. FineganQ1.doc)

Explanation / Answer

Here we are creating a database for school with the tables like

Each table consists of different records storing the related information of the corresponding database like teacher table store the teacher information and class stores class information assignment stores the assignment information etc..

Below are the sql statements which creates the tables in the database with their required fields and their corresponding datatype.

1.create table department(deptno number, deptname varchar2(10),teacherid number (10), PRIMARY KEY (deptno, teacherid) );

2.create table teacher(title varchar2(3),firstname varchar2 (10),middlename varchar2 (10),lastname varchar2 (10), teacherid number(10) PRIMARY KEY,phonenumber number(10),email varchar2(20),class number(2),FOREIGN KEY(teacherid) references teacher (teacherid)) ;

3.create table class(name varchar2 (10) PRIMARY KEY,description varchar2 (20),meetingperiod varchar2(10),roomnumber number(3),teacher number(10) UNIQUE,foreign key(teacherid) references teacher(teacherid) ) ;

4.create table assignment (class varchar2(10) PRIMARY KEY,assignmentdate DATE, duedate DATE, description varchar2(20),teacherid number (10));

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.