database 2

First Normal Form (1NF)

- All domain values in R are atomic - Only atomic (indivisible) values are allowed - No nested relations e.g. The Key

Data Anomalies - Deletion

- If we delete all the shipments of a supplier, the city is gone too

Decompose Relations

- If you have a table INVENTORY and they have a warehouse and an address that are functional dependent and always the same......(looks repeaty) - Make a new table, called location and associate warehouse to address so it can be separated and less redundant

Define Normalization of Relations

- Takes a relation schema through a series of tests -certifies whether it satisfies a certain "normal form" - proceeds in a top-down fashion

How can we measure quality in relational schemas?

- attribute semantics are clear - reducing redundant information in tuples - disallowing possibility of generating spurious tuples

Third Normal Form (3NF)

- if R is 2NF and every non-key attribute is not transitively dependent on the key - Status depends on city, and supplier#, to get it to 3NF we make two seaprate tables to link City to status, and supplier number to city e.g. And Nothing But the Key

Second Normal Form (2NF)

- if R is in 1NF and every non-key (nonprime) attribute is fully dependent on the key - (Part, Warehouse) -> W_Address - since W_address is non-prime and is partially on Part,Warehouse, we must decompose by adding location table linking warehouse to w_adress e.g. The Whole Key

How to identify functional dependencies

- if two tuples have the same X-value, they must have the same Y-value. - for examples, Supplier number -> City

Data Anomalies - Update

- if we change the city of a supplier, we must update all the tuples related to this supplier, if we miss one tuple, the data is no longer consistent

What are the criteria for "good" base relations?

- informal - 1nf, 2nf, 3nf, BCNF

How to achieve First Normal Form (1NF)

- remove nested relation and non-atomic attributes into a new relation - propagate the primary key into it

Data Anomalies - Insertion

- until a supplier actually supplies a part, we can't record (insert) it's city

normalization steps

1) propagate data to eliminate any repeating groups or nested tables 2) show funcitonal dependencies 3) identify candidate key(s) 4) any attributes not FD on Key? - if so, decompose to 1NF 5) Any attributes FD on part of the key? - if so, decompose to 2NF 6) Any non-key attributes FD on non-key? - if so, decompose to 3NF

8. Given only the relation schema Books (*Title*, *Author*, Year, Publisher), we can infer the following functional dependency: (a) Author, Publisher→Publisher (b) Title → Author (c) Year→Publisher (d) Author→Publisher

A

Functional Dependency

A constraint between two attributes in which the value of one attribute is determined by the value of another attribute.

how to write ALTER statement sql (for add column and drop)

ALTER TABLE Company.Employee ADD COLUMN Job VARCHAR(12); or ALTER TABLE Company.Employee DROP COLUMN Job CASCADE/RESTRICT;

A table where every attribute is fully functionally dependent on the key, is said to be in (a) 3NF (b) 2NF (c) 1NF (d) None of the above

B

If the following functional dependencies, ({A}→{B}, {B}→{C}) hold for database schema R(A,B) and S(B,C), then the join of R and S will be (a) Lossy (b) Lossless (c) Non lossless (d) None of the above

B

\

BAH

Show the functional dependencies for some schema NOW

Bitch

A relation schema R is in 3rd Normal Form if (a) Each nonprime attribute in R is fully dependent on every key (b) All attributes in R have atomic domains (c) R satisfies 2nd Normal Form and no nonprime attribute of R is transitively dependent on the primary key (d) R contains only 3 keys

C

Given the set of functional dependencies, ({A, B}→{C, D, E} and {A}→{E}), for relation schema R = (A,B,C,D,E) we can infer the following: (a) {A} is a key for R (b) {B, E} is a key for R (c) {A, B} is a key for R (d) None of the above

C

If a relation R is decomposed into {R1, R2, ..., Rn} and the decomposition is lossless then (a) The natural join of R1, R2, ..., Rn can have more tuples than the original relation for R (b) The relations R1, R2, ...,Rn are each in 3rd Normal Form (c) The natural join of R1, R2,..., Rn will have the same number of tuples as the original relation R (d) None of the above

C

what are the options for a drop sql

CASCADE - remove child and all parents RESTRICT - only remove child if it wont affect parent/master table

table creation sql syntax

CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOTNULL, .... ... )

how to make something a primary key in SQL ? (or UNIQUE)

CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOTNULL, .... ... PRIMARY KEY (FNAME); ) OR CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOTNULL PRIMARY KEY, .... ... )

Create tables for Student(*SSN*, Name, Major, Bdate) Course(*Course#*, Cname, Dept) Enroll(*SSN*, *Course#*,*Quarter*, Grade)

CREATE TABLE Student( *SSN* VARCHAR(9) NOT NULL PRIMARY KEY, Name VARCHAR NOT NULL, Major VARCHAR Bdate DATE NOT NULL ); CREATE TABLE Course( *Course#* VARCHAR NOT NULL PRIMARY KEY, Cname VARCHAR NOT NULL, Dept VARCHAR NOT NULL ); CREATE TABLE Enroll( *SSN* VARCHAR(9) NOT NULL PRIMARY KEY, *Course#* VARCHAR NOT NULL PRIMARY KEY, *Quarter* CHAR(6) NOT NULL PRIMARY KEY, GRADE CHAR(2) FOREIGN KEY (*SSN*) REFERENCES SSN (Student) FOREIGN KEY (*Course#*) REFERENCES Course# (Course) );

how to make a view sql

CREATE VIEW NameIchoose(variables i want to rename in my view) SELECT FROM WHERE

A table that displays data redundancies yields the following anomalies: (a) Update anomalies (b) Insertion anomalies (c) Deletion anomalies (d) All of the above (e) None of the above

D

If {A, B}→{C, D} is one functional dependency that holds for relation schema R(A,B,C,D), then (a) {A, B} is a candidate key for R (b) No two tuples in R can have the same values for both A and B (c) {A, B} is a primary key for R (d) All of the above

D

The functional dependency {A}→{B} for relation schema R(A,B,C,D) implies that (a) No two tuples in R can have the same value for attribute B (b) Any two tuples in R that have the same value for B must have the same value for A (c) No two tuples in R can have the same value for attribute A (d) Any two tuples in R that have the same value for A must have the same value for B

D

The term First Normal Form (1NF) describes the tabular format in which: (a) All the key attributes are defined (b) There are no repeating groups in the table. Row/column intersection can contain one and only one value, not a set of values (c) All attributes are dependent on the primary key (d) All of the above (e) None of the above

D

SQL sub languages

DDL - DATA DEFINITION LANGUAGE DML - DATA MANIPULATION LANGUAGE TCL - TRANSACTION CONTROL LANGUAGE DCL - DATA CONTROL LANGUAGE

how to write DELETE statement sql

DELETE FROM (TABLE) WHERE (Attr) = 'Fukk' AND (Attr2) = 'Huguyaguhhh'

Write appropriate SQL DML statements to delete all of the grades from the student with student number=8 (moresql ica)

DELETE FROM GRADE_REPORT WHERE Student_number = '8';

how to write DROP statement sql

DROP TABLE COMPANY CASCASE/RESTRICT

T/F Full functional dependency means an FD Y → Z where removal of any attribute from Z means the FD does not hold any more

F

T/F A relation schema R is in Second Normal Form (2NF) if every non-prime attribute A in R is functionally dependent (FD) on the primary key

F

T/F A relation schema R is in generalized Third Normal Form (3NF) if when a FD X→A holds in R, then X is a superkey of R and A is a prime attribute of R

F

how to write insert statement SQL

INSERT INTO (TABLE) VALUES ('SHIT', 1, 'BAH')

Write appropriate SQL DML statements to add a course as follows: Enterprise Architecture CSE5235, 3 credit/hrs on the CSE dept. There is one section 221 for Spring 20 taught by Madrid. The prerequisites are CS3320 (moresql ica)

INSERT INTO Course VALUES ('Enterprise Architechture', CSE5235, 3, CSE); INSERT INTO Section VALUES (221, CSE5235, Spring, 20, 'Madrid'); INSERT INTO Prerequisite VALUES (CSE5235, CS3320); and since CSE3320 already has a prereq (CSE1310) we need to INSERT INTO Prerequisite VALUES (CSE5235, CSE1310);

How to solve Deletion anomaly

In separate table enter information that would be lost when something is deleted (if a supplier has only one part rn) e.g. Link Supplier# to part# to quantity (separate from Supplier#, Status, City) so that only the part is deleted and not the location

How to solve Insertion anomaly

In separate table enter information that would be non-existent when there's an insertion (like location) even if the "supplier" doesn't have any "parts"

Get From 2NF to 3NF (correction)

Look for attributes that aren't the primary key that depend on each other, and fix it, separate them into different tables

T/F NOT (Ex) (P(x)) -> Vx(NOT (P(x))

P = hairy x = person/people If a person that's hairy does NOT exist, then all people are not hairy! True.

T/F (Ex) (P(x)) -> Vx((P(x))

P = ugly x = dog/dogs If there exists a dog that is ugly, then all dogs are ugly! False.

ORDER BY SQL

SELECT * FROM Employee ORDER BY Lname DESC, Fname, Super_ssn will order the employee table by Last name from Z to A (descending)

List the actors who appeared in The Dark Knight, but were not in the star role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role] (evenmoresql ica)

SELECT Actor.name FROM Actor, Movie, Casting_Table WHERE Movie.title = 'The Dark Knight' AND Movie.id = Casting_Table.movieid AND Casting_Table.ord > 1 AND Casting_Table.actorid = Actor.id

how to write SELECT staetments for sql retrieval (IF YOU WANT ONLY ONE TUPLE(PERSON)

SELECT Bday, Addr FROM Employee WHERE Fname = 'Jack' AND Lname = 'Fukker';

Write a query in SQL that will list the course name and instructor, for all of the course sections, in alphabetical order by instructor (moresql ica)

SELECT Course_name, Instructor FROM Course, Section WHERE Course.Course_number = Section.Course_Number ORDER BY Instructor

how to select distict in case we're getting dupes from one table

SELECT DISTINCT(Salary) From Employee; only returns unique salaries

Nested sql

SELECT Essn FROM Works_On WHERE (Pno, Hours) IN (SELECT Pno, Hours FROM Works_On WHERE Essn='123456789')

how to search for a value wildcard in sql (if you want something like an attribute, but not an exact match)

SELECT Fname, LName FROM Employee WHERE Lname LIKE 'Smith%'; % at the end -> Smith, Smithton, Smithfield..etc Select Pname FROM Products WHERE Discount LIKE '%5; % at the beginning -> 5, 25 , 95 SELECT Fname, Lname FROM Employee WHERE Bdate LIKE ' _ _ 5 _ _ _ _ _ _ _ ' ; 5 in the _ _ 5 _ part of the year will retreive all employees born during the 1950's

how to write INNER JOIN ( we want first and last name of all employees who work in a specific department as well)

SELECT Fname, Lname FROM Employee, Dept WHERE Dname = 'Research' AND Dnumber = Dno; if they have similar attribute names,use aliases (upper level classes almsot) SELECT Fname, Lname FROM Employee, Dept WHERE Department.DName = 'Research' AND Department.DNumber = Employee.DNumber;

how to write SELECT staetments for sql retrieval (IF YOU WANT HE WHOLE TABLE)

SELECT Fname, Lname FROM Employee;

how does cross join behavior occur and how do we notice it

SELECT Fname, Lname, Dname FROM Employee, Department this will just join the tables and we arent specifying anything so if someone's in two departments, it'll duplicate

List the film title, budget, and the leading actor for all of the films with a budget within the range 5,000,000 to 20,000,000. List the films in descending order by their budget. (evenmoresql ica)

SELECT Movie.title, Movie.budget, Actor.name FROM Movie, Actor, Casting_Table WHERE Movie.id = Casting_Table.movieid AND Casting_Table.ord = '1' AND Actor.id = Casting_Table.actorid AND movieid IN ( SELECT id FROM Movie WHERE budget > 5,000,000 AND budget < 20,000,000 ORDER BY budget DESC ;

Write a query in SQL to list all of the student names, course names, section and their grade (moresql ica)

SELECT Name, Course_name, Section_identifier, Grade FROM Student, Course, Section, Grade_Report WHERE Student.Student_Number = Grade_Report.Student_number AND Grade_Report.Section_identifier = Section.Section_identifier AND Section.Course_number = Course.Course_number ;

Write a query in SQL to list all of the student names and numbers that got an 'A' or 'B' in a course. (moresql ica)

SELECT Name, Student_number FROM Student, Grade_Report WHERE Student.Student_number = Grade_Report.Student_number AND Student_number = 'A' OR Student_number = 'B' ;

how to write INNER JOIN (ternary) we want the names of the projects in Houston and the last names of the managers of these projects

SELECT Pname, Lname FROM Employee, Project, Department first join employee and department ( so we can get last name after we find managers) WHERE Department.Dnumber = Employee.Dno now we need to get just managers (department has mgr ssn) AND Department.Mgr_Ssn = Employee.Ssn now projs that that manager has in hosuton AND Project.Plocation = 'Houston';

Obtain the actor with the most non starring roles and the number of movies the actor starred in. (evenmoresql ica)

SELECT name, MAX(cnt) as Count_Least_Roles FROM (Select name, COUNT(*) as cnt FROM Actor, Casting WHERE id = actorid AND ord > 1 GROUP BY actorid );

wildcard sql

Select * From (Niner)

T/F A BCNF relation is always in 3rd Normal Form

T

T/F A Prime attribute must be a member of some candidate key

T

T/F A relation schema R is in Third Normal Form (3NF) if it is in 2NF and no non-prime attribute in R is transitively dependent on the primary key

T

T/F A relation where all attributes are atomic is always in 1st Normal Form

T

T/F Transitive functional dependency means a FD X→Z that can be derived from two FDs X→Y and Y→Z

T

T/F X→Y holds if whenever two tuples have the same value for X, they must have the same value for Y

T

What is relational database design?

The grouping of attributes to form "good" relation schemas

how to write UPDATE Statement sqsl

UPDATE (TABLE) SET (Address) = 'FUK OMG SHIT Drive' WHERE (Ssn) = 72132938 also can iterate UPDATE (TABLE) SET (SALARY) = SALARY + 1 WHERE (Ssn) = 72132938

Write appropriate SQL DML statements to change all of the sections of CS1310 to Spring 18: (moresql ica)

UPDATE Section SET Semester = 'Spring', Year = '18' WHERE Course = 'CS1310' );

what commands does SQL - TCL have

commit rollback

what commands does SQL - DDL have

create drop rename alter

How to identify data redundancy?

if a column marked "city" has london like 10 times and paris twice - waste of storage -

Full functional dependency vs Partial dependency let x->y (y depends on x)

if y depends on x, then y also depends on a subset of x ( If this happens, its not a full functional, (it doesnt really depend on both) its a PARTIAL)

How to solve Update anomaly

if you want to update the city for a supplier, since you will have them in a separate table, (Supplier#, status, city) you can just update one tuple.

natural key vs composite key vs surrogate

natural - key that is formed of attributes that already exist in the real world composite - key of two or more attributes surrogate - key with no business meaning

Prime attribute vs nonprime

prime - member of some candidate key nonprime - all other attributes

what commands does SQL - DML have

update delete insert