Problem 1

Define the following terms: relation schema, relational database schema, domain, attribute, attribute domain, relation instance, relation cardinality, and relation degree.

• Relation Schema: The basic "template" which data uses in a relation, it defines the columns and their data types as well as any constraints.
• Relational Database Schema: A set of relation schemas which describe the whole "template" of the database.
• Domain: The permissible values for something.
• Attribute: A column in a relation schema.
• Attribute Domain: The permissible values of a column (a given attribute).
• Relation Instance: A tuple formed of the same schema as a relation schema.
• Relation Cardinality: Number of tuples in a given relation schema.
• Relation Degree: Number of columns in a given relation schema.

Problem 2

How many distinct tuples are in a relation instance with cardinality 22?

There are 22 distinct tuples

Problem 3

Does the relational model, as seen by an SQL query writer, provide physical and logical data independence? Explain.

Yes it does the sql query writer never needs to know the specifics about how the data is stored only logically how it's related.

Problem 4

What is the difference between a candidate key and the primary key for a given relation? What is a super key?

Candidate keys are the minimal spanning columns which can uniquely identify a given tuple, primary keys are a single candidate key the database designer chooses to identify a tuple. Super keys are any set of columns that can uniquely identify all tuples, they can notable contain redundant columns that are not necessarily needed to identify all the tuples.

Problem 5

sid	name	login	age	gpa
50000	Dave	dave@cs	19	3.3
53666	Jones	jones@cs	18	3.4
53688	Smith	smith@ee	18	3.2
53650	Smith	smith@math	19	3.8
53831	Madayan	madayan@music	11	1.8
53832	Guldu	guldu@music	12	2.0

Consider the instance of the Students relation shown above.

(a). Give an Example of an Attribute (or Set of attributes) that You Can Deduce is not a Candidate Key, Based on This Instance Being Legal

name can not be a candidate key because it's not unique additionally any super keys including name can also not be candidate keys as they wouldn't be minimally spanning.

(b). Is there Any Example of an Attribute (or Set of attributes) that You Can Deduce is a Candidate Key, Based on This Instance Being Legal?

No, you cannot determine a key of a relation given only one instance of the relation

Problem 6

What is a foreign key constraint? Why are such constraints important? What is referential integrity?

A foreign key constraint is a constraint that requires the specified field to be a primary key of another relation. Referential integrity defines that if an attribute references another relation through a foreign key constraint it must exist.

Problem 7

Answer each of the following questions briefly. The questions are based on the following relational schema:

• Emp(eid: integer, ename: string, age: integer, salary: real)
  • PK: eid
• Works(eid: integer, did: integer, pcttime: integer)
  • PK: (eid, did)
• Dept(did: integer, dname: string, budget: real, managerid: integer)
  • PK: did

(a). Give an Example of a Foreign Key Constraint that Involves the Dept Relation. what Are the Options for Enforcing This Constraint when a User Attempts to Delete a Dept Tuple?

The did column of Works should have a foreign key constraint involving the Dept relation. We can either delete the Works tuple that references the deleted tuple or we can set it's value to NULL, in this case deleting the tuple makes more sense as employees who work at departments that no longer exists typically wouldn't be tracked.

Additionally the managerid field should reference Emp and on delete we should restrict deletions so that the user knows a manager must be appointed before we can replace them.

(b). Write the Sql Statements Required to Create the Preceding Relations, including Appropriate Versions of All Primary and Foreign Key Integrity Constraints

CREATE TABLE Emp (
	eid INTEGER PRIMARY KEY,
	
	ename VARCHAR(100),
	age INTEGER,
	salary REAL
);

CREATE TABLE Dept (
	did INTEGER PRIMARY KEY,
	
	dname VARCHAR(100),
	budget REAL,
	managerid INTEGER,
	
	FOREIGN KEY (managerid) REFERENCES Emp(eid) ON DELETE RESTRICT
);

CREATE TABLE Works (
	eid INTEGER,
	did INTEGER,
	
	pcttime INTEGER,
	
	FOREIGN KEY (eid) REFERENCES Emp(eid) ON DELETE CASCADE,
	FOREIGN KEY (did) REFERENCES Dept(did) ON DELETE CASCADE,
	
	PRIMARY KEY (eid, did)
);

(c). Define the Dept relation in Sql so that Every Department is Guaranteed to Have a Manager

CREATE TABLE Dept (
	did INTEGER PRIMARY KEY,
	
	dname VARCHAR(100),
	budget REAL,
	managerid INTEGER NOT NULL,
	
	FOREIGN KEY (managerid) REFERENCES Emp(eid) ON DELETE RESTRICT
);

(d). Write an Sql Statement to Add John Doe as an Employee with eid = 101, age = 32 and salary = 15,000

INSERT INTO Emp (
	101,
	'John Doe',
	32,
	1500
);

(e). Write an Sql Statement to Give Every Employee a 10 Percent Raise

UPDATE Emp SET salary = salary * 1.10;

(f). Write an Sql Statement to Delete the Toy Department. given the Referential Integrity Constraints You Chose for This Schema, Explain what Happens when This Statement is Executed

DELETE FROM Dept WHERE dname = 'Toy';

Any employees working in the toy department will have their entries in Works deleted meaning they no longer work at the Toy department as it no longer exists.

Problem 8

sid	name	login	age	gpa
53831	Madayan	madayan@music	11	1.8
53832	Guldu	guldu@music	12	2.0

Students with age < 18 on Instance S

Consider the SQL query whose answer is shown above.

(a). Modify This Query so that only the Login Column is Included in the Answer

SELECT login FROM Students
WHERE age < 18;

(b). If the Clause where S.gpa >= 2 is Added to the Original Query, what is the Set of Tuples in the Answer?

sid	name	login	age	gpa
53832	Guldu	guldu@music	12	2.0

Madayan is removed from the result as her gpa is below 2.

Problem 9

Suppose that we have a ternary relationship R between entity sets A, B, and C such that A has a key constraint and total participation and B has a key constraint; these are the only constraints. A has attributes a1 and a2, with a1 being the key; B and C are similar. R has no descriptive attributes. Write SQL statements that create tables corresponding to this information so as to capture as many of the constraints as possible. If you cannot capture some constraint, explain why.

CREATE TABLE B (
	b1 INTEGER PRIMARY KEY,
	b2 INTEGER
);

CREATE TABLE C (
	c1 INTEGER PRIMARY KEY,
	c2 INTEGER
);

CREATE TABLE A (
	a1 INTEGER PRIMARY KEY,
	a2 INTEGER,
	
	b1 INTEGER UNIQUE NOT NULL,
	c1 INTEGER NOT NULL,
	
	FOREIGN KEY (b1) REFERENCES B(b1),
	FORIEGN KEY (c1) REFERENCES C(c1)
);

Problem 10

Consider the following scenario, where you design a university database. Write SQL statements to create the corresponding relations and capture as many of the constraints as possible. If you cannot capture some constraints, explain why.

• Professors have an SSN, a name, an age, a rank, and a research specialty.
• Projects have a project number, a sponsor name (e.g., NSF), a starting date, an ending date, and a budget.
• Graduate students have an SSN, a name, an age, and a degree program (e.g., M.S. or Ph.D.).
• Each project is managed by one professor (known as the project’s principal investigator). Each project is worked on by one or more professors (known as the project’s co-investigators).
• Professors can manage and/or work on multiple projects.
• Each project is worked on by one or more graduate students (known as the project’s research assistants).
• When graduate students work on a project, a professor must supervise their work on the project. Graduate students can work on multiple projects, in which case they will have a (potentially different) supervisor for each one.
• Departments have a department number, a department name, and a main office.
• Departments have a professor (known as the chairman) who runs the department.
• Professors work in one or more departments, and for each department that they work in, a time percentage is associated with their job.
• Graduate students have one major department in which they are working on their degree.
• Each graduate student has another, more senior graduate student (known as a student advisor) who advises him or her on what courses to take.

ER Diagram of the Scenario

CREATE TABLE Professor (
	ssn INTEGER PRIMARY KEY,
	
	name VARCHAR(20),
	age INTEGER,
	
	specialty VARCHAR(20),
	rank VARCHAR(20)
);

CREATE TABLE Department (
	departmentNumber INTEGER PRIMARY KEY,
	
	name VARCHAR(20),
	mainOffice VARCHAR(100),
	
	ranBy INTEGER NOT NULL,
	
	FOREIGN KEY (ranBy) REFERENCES Professor(ssn) ON DELETE RESTRICT
);

CREATE TABLE WorksIn (
	ssn INTEGER NOT NULL,
	departmentNumber INTEGER NOT NULL,
	
	timePercentage INTEGER,
	
	FORIEGN KEY (ssn) REFERENCES Professor(ssn)
	ON DELETE CASCADE,
	FOREIGN KEY (departmentNumber) REFERENCES Department(departmentNumber)
	ON DELETE CASCADE,
	
	PRIMARY KEY (ssn, departmentNumber)
);

CREATE TABLE GraduateStudent (
	ssn INTEGER PRIMARY KEY,
	
	name VARCHAR(20),
	age INTEGER,
	
	degreeProgram VARCHAR(20),
	
	major INTEGER NOT NULL,
	advisor INTEGER,
	
	FOREIGN KEY (major) REFERENCES Department(departmentNumber),
	FOREIGN KEY (advisor) REFERENCES GraduateStudent(ssn)
);

CREATE TABLE Project (
	projectNumber INTEGER PRIMARY KEY,
	
	sponsorName VARCHAR(20),
	budget INTEGER,
	
	managedBy INTEGER NOT NULL,
	
	startingDate DATE,
	endingDate DATE,
	
	FOREIGN KEY (managedBy) REFERENCES Professor(ssn)
);

CREATE TABLE ProfessorWorksOn (
	ssn INTEGER NOT NULL,
	projectNumber INTEGER NOT NULL,
	
	FORIEGN KEY (ssn) REFERENCES Professor(ssn)
	ON DELETE CASCADE,
	FOREIGN KEY (projectNumber) REFERENCES Project(projectNumber)
	ON DELETE CASCADE,
	
	PRIMARY KEY (ssn, projectNumber)
);

CREATE TABLE GraduateWorksOn (
	ssn INTEGER NOT NULL,
	projectNumber INTEGER NOT NULL,
	
	supervisorSSN INTEGER NOT NULL,
	
	FORIEGN KEY (ssn) REFERENCES GraduateStudent(ssn)
	ON DELETE CASCADE,
	FOREIGN KEY (projectNumber) REFERENCES Project(projectNumber)
	ON DELETE CASCADE,
	FOREIGN KEY (supervisorSSN) REFERENCES Professor(ssn)
	ON DELETE RESTRICT,
	
	PRIMARY KEY (ssn, projectNumber)
);

The total participation of Professor with WorksIn can not be expressed as it has a many to many relationship, the same is true for the participation of Project with WorksOn.

Problem 11

Consider the following scenario, where you design a company database for a music records corporation. Write SQL statements to create the corresponding relations and capture as many of the constraints as possible. If you cannot capture some constraints, explain why.

• Each musician that records at Notown has an SSN, a name, an address, and a phone number. Poorly paid musicians often share the same address, and no address has more than one phone.
• Each instrument used in songs recorded at Notown has a unique identification number, a name (e.g., guitar, synthesizer, flute) and a musical key (e.g., C, B-flat, E-flat).
• Each album recorded on the Notown label has a unique identification number, a title, a copyright date, a format (e.g., CD or MC).
• Each song recorded at Notown has a title and an author.
• Each musician may play several instruments, and a given instrument may be played by several musicians.
• Each album has a number of songs on it, but no song may appear on more than one album.
• Each song is performed by one or more musicians, and a musician may perform a number of songs.
• Each album has exactly one musician who acts as its producer. A musician may produce several albums, of course.

ER Diagram of the Scenario

CREATE TABLE HomePhone (
	homeAddress VARCHAR(20) PRIMARY KEY,
	phoneNumber INTEGER NOT NULL,
);

CREATE TABLE Musician (
	ssn INTEGER PRIMARY KEY,
	
	name VARCHAR(20),
	homeAddress VARCHAR(20) NOT NULL,
	
	FOREIGN KEY (homeAddress) REFERENCES HomePhone(homeAddress) ON DELETE RESTRICT
);

CREATE TABLE Album (
	idNumber INTEGER PRIMARY KEY,
	
	title VARCHAR(20),
	format VARCHAR(20),
	
	copyrightDate Date,
	
	producedBy INTEGER NOT NULL,
		
	FOREIGN KEY (producedBy) REFERENCES Musician(ssn) ON DELETE CASCADE
);

CREATE TABLE Song (
	title VARCHAR(20) PRIMARY KEY,
	
	author VARCHAR(20),
	apartOf INTEGER NOT NULL,
	
	FOREIGN KEY (apartOf) REFERENCES Album(idNumber) ON DELETE CASCADE
);

CREATE TABLE PerformedBy (
	musician INTEGER NOT NULL,
	song VARCHAR(20) NOT NULL,
	
	FOREIGN KEY (musician) REFERENCES Musician(ssn)
	ON DELETE CASCADE,
	FOREIGN KEY (song) REFERENCES Song(title)
	ON DELETE CASCADE,
	
	PRIMARY KEY (musician, song)
);

CREATE TABLE Instrument (
	idNumber INTEGER PRIMARY KEY,
	
	name VARCHAR(20),
	key VARCHAR(20)
);

CREATE TABLE PlaysInstrument (
	musician INTEGER NOT NULL,
	instrument INTEGER NOT NULL,
	
	FOREIGN KEY (musician) REFERENCES Musician(ssn)
	ON DELETE CASCADE,
	FOREIGN KEY (instrument) REFERENCES Instrument(idNumber)
	ON DELETE CASCADE,
	
	PRIMARY KEY (musician, instrument)
);

The constraints that could not be included were that albums must have at least one song as we can't enforce that on the many side, same with songs needing to be performed by at least one musician.