Week 6

Post date: Feb 10, 2019 4:51:48 AM

/!\ In-Class Assignment 1 will be on Wednesday

Chapter 6: The Relational Algebra and Relational Calculus (Continued)

Binary relational operations: JOIN and DIVISION

Additional Relational Operations

Chapter 4: Basic SQL

SQL data definition and data types

Specifying constraints in SQL

Basic retrieval queries in SQL

INSERT, DELETE, and UPDATE statements in SQL

Simplified DDL SQL:

CREATE SCHEMA schema_name;

USE schema_name;

CREATE TABLE table_name (

columnName1 datatype1 [PRIMARY KEY],

columnName2 datatype2,

...

[PRIMARY KEY (columnName1, columnName2, ...),]

CONSTRAINT constraint2_name FOREIGN KEY (columnName) REFERENCES other_table_name(other_table_column)

);

DROP TABLE IF EXISTS table_name;

ALTER TABLE table_name

ADD CONSTRAINT ...,

DROP COLUMN ...

...;

Lab Exercises:

Settings:

MySQL Connection Details: Connection name: YOUR NAME

Hostname: 10.32.33.54

Port: 3306

username: uFULLID

password: FULLID (STORE IN VAULT)

YOUR SCHEMAS HAVE TO START WITH: sFULLID

Exercises:

Ex 1: Create the following tables:

• Person(driver-id, address, name)

• Car(license, model, year, #driver-id)

• Accident(report-number, location, date)

• Participated(#driver-id, #license, #report-number, damage-amount)

Ex 2: Create the following tables:

• Student(StudentNum, SSN, Class, Sex, Dirthdate, FN, MN, LN, StreetAddress, City, State, Zip, CPhone, CAddress, PPhone, DegreeProgram, #DCode_Major, #DCode_Minor)

• Department(DCode, Dname, College, DOffice, DPhone)

• Course(CNum, CName, CDescription, SemesterHours, Level, #DCode)

• Section(#CNum, SectionNum, Semester, Year, Instructor)

• Grade(NumericGrade, LetterGrade)

• Stud-Sect(#StudentNum, #CNum, #SectionNum, #Semester, #Year, #NumericGrade)

Ex 3: Write the SQL DDL commands in order to create the following schemas: