You are doing a consulting project for an educational institution and want to co

Question

You are doing a consulting project for an educational institution and want to convince them to utilize your software. From the previous lab, you realized how tedious and time consuming it is to create the records and enter data into your tables. It’s even more time consuming if bad data enters the equation, lets prevent that from happening. With this lab, you are to ensure that only GOOD data enters your records by means of constraints. Below is a listof integrity and value constraints that need to be added to your current record formats. You should not change your previous lab, this should be appended to the end of lab 1. Integrity Constraints STUDENT -StudentID - primary key COURSE - CourseID - primary key LOCATION - LocationID -primary key FACULTY - FacultyID - primary key SECTION -SectionID – primary key SECTION -CourseID - foreign key SECTION -LocationID - foreign key Check Constraints STUDENT - ZIP –only 0-9 and ‘-‘ only valid characters (US zip code standard) SECTION - SEATS AVAILABLE < 100 COURSE - CREDIT HOURS <= 6.0 SECTION - STARTTIME MUST BE PRIOR TO ENDTIME STATUS – ‘A’ or ‘I’ except below o Student Table – Valid values are ‘C’ and ‘T’ (Current, Transfer) o Faculty Table - Valid values are ‘F’, ‘A’ (Full-time, Adjunct) Formatting Concerns: CSCI 2370 - Database Systems Programming Professor Shawn M. Ferguson Page 2 Section Table -(no constraint needed) STARTTIME & ENDTIME –When reporting these fields out break them up into separate fields that hold the time only and date only. You will not change the table, you will use format models to report the data into 4 different fields (StartTime/StartDate and EndTime/EndDate). For example, the field called StartTime will be reported like this: StartTime – 6:00PM -HH:MI AM format StartDate – 01/04/2014 -MM/DD/YYYY format Remember: You are not changing the table structure, only the report (query) that displays the data. Assignment: Use the UPDATE statement to alter the data from Lab 1 to create new data that meet the criteria for the constraints. At a minimum you will need to use an UPDATE statement for the StartTime/EndTime field to include the time portion, we do not want classes to start at midnight (12:00AM). TURN IN: An updated script file containing all the constraints and Update statements used to alter data. Please also include the same reports and an additional report from the section table showing the Dates/Times in the format describe above. You should only turn in 1 script file thatcontains everything for this lab. Include comments that provide a description of specific sections of the lab.

this is lab1 that i did

DROP TABLE sTUDENT;
CREATE TABLE STUDENT (
StudentID NUMBER(10),
FirstName VARCHAR2(20),
LastName VARCHAR2(20),
MI CHAR(1),
Address VARCHAR2(20),
City VARCHAR2(20),
State CHAR(2),
Zip VARCHAR2(10),
HomePhone VARCHAR2(10),
WorkPhone VARCHAR2(10),
Email VARCHAR2(20) ,
DOB DATE,
PIN VARCHAR2(10),
Status CHAR(1)
);
DROP TABLE FACULTY;
CREATE TABLE FACULTY(
FacultyID Number(10),
FIRSTNAME VARCHAR2(20),
LASTNAME VARCHAR2(20),
MI CHAR(1),
WORKPHONE VARCHAR2(10),
CellPhone VARCHAR2(10),
Rank VARCHAR2(20),
Experience VARCHAR2(20),
Status CHAR(10)
);
DROP TABLE COURSE;
cREATE TABLE COURSE(
CourseID Number(10),
CourseNumber Varchar2(20),
CourseName Varchar(20),
Description Varchar(20),
CreditHours Number(4),
Status Char(1)
);
DROP TABLE SECTION;
cREATE TABLE SECTION(
SectionID Number(10),
courseID Number(10),
sectionNumber Varchar2(10),
Days Varchar2(10),
StartTime Date,
Endtime Date,
LocationID Number(10),
SeatAvailable Number(3),
Status Char(1)
);
DROP TABLE LOCATION;
CREATE TABLE LOCATION (
LocationID Number(10),
Building Varchar2(20),
Room Varchar2(5),
capacity Number(5),
Status char(1)
);
DROP SEQUENCE S_id;
CREATE SEQUENCE S_ID
MINVALUE 1
MAXVALUE 99999
START WITH 1
INCREMENT BY 1
CACHE 20;
DROP INDEX i_sTUDENT_first_Name;
CREATE INDEX i_Student_first_Last_Name on
Student(lastname);

Explanation / Answer

Answer: See the script below:

----------------------------------------------

ALTER TABLE STUDENT ADD Primary Key (StudentID);
ALTER TABLE STUDENT ADD CHECK (REGEXP_LIKE(Zip,'^[[:digit:]]-[[:digit:]]$'),
                        CHECK (Status='C' OR Status='T');
ALTER TABLE COURSE ADD Primary Key (CourseID);
ALTER TABLE COURSE ADD CHECK (CreditHourse <= 6.0),
                       CHECK (Status='A' OR Status='I');
ALTER TABLE LOCATION ADD Primary Key (LocationID);
ALTER TABLE LOCATION ADD CHECK (Status='A' OR Status='I');
ALTER TABLE FACULTY ADD Primary Key (FacultyID);
ALTER TABLE FACULTY ADD CHECK (Status='F' OR Status='A');
ALTER TABLE SECTION ADD Primary Key (SectionID);
ALTER TABLE SECTION ADD Foreign Key (CourseID) REFERENCES COURSE(CourseID);
ALTER TABLE SECTION ADD Foreign Key (LocationID) REFERENCES LOCATION(LocationID),
                       CHECK (SeatAvailable < 100),
                       CHECK (StartTime < EndTime),
                       CHECK (Status='A' OR Status='I');

----------------------------------------------------------

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

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