ITSE 2309 – Database Programming: Oracle Lab 11 – Final Project All I need is he

Question

ITSE 2309 – Database Programming: Oracle Lab 11 – Final Project

All I need is help with 4-6. This is what I did so far:

School Management Database System Design a database system to support any number of school in an area (e-g. School District. Town, or City). The entities should include, Attendance. Classes, Parents Schools, Students, and Teachers. 1. Write the business rule(s) that govern the relationship between the entities 2. Given the business rule(s) you wrote in Step 1, create a Crows Foot ERD. Do not 2. Given the business rule(s) you wrote in Step 1, create a Crow's Foot ERD. Do not use your free hand to create the ERD 3. Determine the characteristics of each entity (Attributes) 4. Identify the Primary and Foreign keys and any other constraints for each entity 5. Make sure they are in 1NF, 2NF, and 3NF 6. Write DDLs, in a file, to create tables for your database and populate them. See the JustLee scripts. 7. Create a folder and copy all your work there. Zip the folder, rename it to your Fullname-Final-Project.zip, and then upload it to Blackboard.

Explanation / Answer

4)

As per your databse requirement,here i'm extending database schema as follows. And representing primary keys as(pk) and foreign keys as (fk).

Student(student_id(pk),fname,lname,dob,phone,parent_id,email,street,city,state,zip)
Parent(Parent_id(pk),fname,lname,email,phone,student-id(fk),street,city,state,zip)
Class(class_id(pk),name,description)
attendance(date(pk) ,student_id(fk),status,percentage,remarks)
Teacher(Teacher_id(pk) ,email,fname,class_id(fk),lname,dob,phone,status,street,city,state,zip)

5)
1NF: No two Rows must contain repeating information.

The above relational tables are already in 1NF.

2NF:
It should be in 1NF
There must not be any partial dependency of any column on primary key.

3NF:
It should be in 2NF
Every non-prime attribute of table must be dependent on primary key.

By applying 2Nf and 3NF we can reconstruct above relational tables as follows:

Student(student_id(pk),fname,lname,dob,phone,email,zip)
Parent(Parent_id(pk),fname,lname,email,phone,student-id(fk),zip)
Class(class_id(pk),name,description)
attendance(date(pk) ,student_id(fk),status,percentage,remarks)
Teacher(Teacher_id(pk) ,email,fname,class_id(fk),lname,dob,phone,zip)
Address(zip(pk),street,city,state)

FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)

6)

Creation of student table:
.....................................
CREATE TABLE Student (
student_id int NOT NULL,
fname VARCHAR2(255),
lname VARCHAR2(255),
dob DATE,
Phone NUMBER,
email VARCHAR2(255),
Zip NUMBER,
PRIMARY KEY (student_id),
FOREIGN KEY (zip) REFERENCES Address(ZIP)
);

Creation of parent table:
.....................................

CREATE TABLE Parent (
parent_id int NOT NULL,
fname VARCHAR2(255),
lname VARCHAR2(255),
Phone NUMBER,
email VARCHAR2(255),
Zip NUMBER,
PRIMARY KEY (parennt_id),
FOREIGN KEY (student_id) REFERENCES Student(student_id)
FOREIGN KEY (zip) REFERENCES Address(ZIP)
  
);

Creation of Class table:
.....................................

CREATE TABLE Class (
class_id int NOT NULL,
name VARCHAR2(255),
description VARCHAR2(255),
PRIMARY KEY (Class_id),
);

Creation of Class Attendance:
.....................................

CREATE TABLE Attendance (
Date date,
Student_id int NOT NULL,
status VARCHAR2(255),
percentage number,
remarks VARCHAR2(255),
PRIMARY KEY (Date),
FOREIGN KEY (student_id) REFERENCES Student(student_id)
  
);

Creation of Teacher table:
.....................................
CREATE TABLE Teacher (
teacher_id int NOT NULL,
fname VARCHAR2(255),
class_id int NOT NULL,
dob DATE,
Phone NUMBER,
email VARCHAR2(255),
Zip NUMBER,
PRIMARY KEY (teacher_id)
FOREIGN KEY (class_id) REFERENCES class(class_id)
FOREIGN KEY (zip) REFERENCES Address(ZIP)
);

Creation of Address table:
.....................................
CREATE TABLE Address (
ZIP number NOT NULL,
street VARCHAR2(255),
city VARCHAR2(255),
state VARCHAR2(255),
PRIMARY KEY (ZIP)
FOREIGN KEY (class_id) REFERENCES class(class_id)
);

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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