1 / 14

College Database Exercise

College Database Exercise. College Database. Students take courses (0 – many) Faculty members teach courses (0 – many) No course is taught by more than one instructor Two major subtypes of students Undergraduate Graduate A course is assigned to one classroom

britanni-lowe
Download Presentation

College Database Exercise

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. College Database Exercise

  2. College Database • Students take courses (0 – many) • Faculty members teach courses (0 – many) • No course is taught by more than one instructor • Two major subtypes of students • Undergraduate • Graduate • A course is assigned to one classroom • Some courses (e.g., independent study) don’t have a classroom assigned

  3. ER Diagram Student takes Course teaches Faculty meets in d Under Grad Classroom

  4. ER Model in Oracle

  5. Visio Rendition

  6. STUDENT SID Name Major Year GPA REGISTRATION RID SID CID COURSE CID Title IID Hours CR_ID INSTRUCTOR CLASSROOM IID Name Dept CR_ID Description Capacity Relational Model Diagram

  7. Access Tables

  8. REGISTRATION STUDENT COURSE CLASSROOM INSTRUCTOR

  9. Normalization

  10. Oracle Tables • Transforming ERD to DB in Oracle • See PowerPoint slides “Transforming ERD to Tables in Oracle” (WebCT) • Creating Tables using SQL CREATE TABLE STUDENT(SID VARCHAR(5) NOT NULL,NAME VARCHAR(50) NOT NULL,MAJOR VARCHAR(30),GPA DECIMAL(5,2) CONSTRAINT SID_PK PRIMARY KEY (SID));

  11. CREATE TABLE DEMO_STUDENT (SID VARCHAR(5) NOT NULL, NAME VARCHAR(50) NOT NULL, MAJOR VARCHAR(30), YEAR INTEGER, GPA DECIMAL(5,2), CONSTRAINT STU_PK PRIMARY KEY (SID)); CREATE TABLE DEMO_INSTRUCTOR (IID VARCHAR(5) NOT NULL, NAME VARCHAR(50) NOT NULL, DEPT VARCHAR(30), CONSTRAINT INST_PK PRIMARY KEY (IID)); CREATE TABLE DEMO_CLASSROOM (CRID VARCHAR(5) NOT NULL, DESCR VARCHAR(50) NOT NULL, CAP INTEGER, CONSTRAINT CLSR_PK PRIMARY KEY (CRID));

  12. CREATE TABLE DEMO_COURSE (CID VARCHAR(5) NOT NULL, TITLE VARCHAR(50), IID VARCHAR(5) NOT NULL, HOUR INTEGER, CRID VARCHAR(30), CONSTRAINT COURSE_PK PRIMARY KEY (CID), CONSTRAINT COUR_FKDISP1 FOREIGN KEY (IID) REFERENCES DEMO_INSTRUCTOR(IID), CONSTRAINT COUR_FK2 FOREIGN KEY (CRID) REFERENCES DEMO_CLASSROOM(CRID)); CREATE TABLE DEMO_REGISTRATION (RID VARCHAR(5) NOT NULL, SID VARCHAR(5) NOT NULL, CID VARCHAR(5) NOT NULL, CONSTRAINT REG_PK PRIMARY KEY (RID), CONSTRAINT REG_FK1 FOREIGN KEY (SID) REFERENCES DEMO_STUDENT(SID), CONSTRAINT REG_FK2 FOREIGN KEY (CID) REFERENCES DEMO_COURSE(CID));

  13. Oracle Database • Inserting Values into a Table INSERT INTO STUDENT(‘S01’, ‘Bird’, ‘Philosophy’, 2, 3.5); INSERT INTO STUDENT(‘S02’, ‘Austin’, ‘English’, 2, 3.7); INSERT INTO STUDENT(‘S03’, ‘Scott’, ‘Statistics’, 3, 2.5); INSERT INTO STUDENT(‘S04’, ‘Coleman’, ‘Business’, 1, 1.4); INSERT INTO STUDENT(‘S05’, ‘Jefferson’,NULL, 2, 1.8); . . . . .

  14. Oracle Database • Creating Views CREATE VIEW BUSINESS_STUDENT ASSELECT *FROM STUDENTWHERE MAJOR = ‘Business’; CREATE VIEW CLASS_ROLL_C01SELECT STUDENT.SID, NAME, MAJOR, YEAR, CID FROM STUDENT, REGISTRATION WHERE CID = ‘C01’;

More Related