C170 - Data Management Applications

A -> B

- B is dependent on A - B can be determined from A - A relates to AT MOST 1 value of B - Ex: SSN -> Name - Name is dependent on SSN - Name can be determined from SSN - SSN cannot be determined from Name

A table has: - columns A, B, C, D - candidate keys (C, D) and A - primary key (C, D) - dependencies on (C, D) and A only What is the normal form of the table?

- Boyce-Codd - The table is in Boyce-Codd normal form since all dependencies are on candidate keys. - Therefore, all dependencies are on unique columns.

What is the effect on the primary key table when you delete the row in the foreign key table?

- Deleting the foreign key in the foreign key table has no efect on the primary key table

CREATE TABLE Employee ( ID SMALLINT UNSIGNED, Name VARCHAR(60), BirthDate DATE, Salary DECIMAL(7,2) ); TRUE or FALSE: The Salary column hold only non-negative numbers?

- FALSE - DECIMAL(7,2) is by default a signed number unless the UNSIGNED keyword is specified, so the number may be negative.

True or False: An entity-relationship model is developed for all database design projects.

- False - An entity-relationship model is developed in the analysis phase. Analysis is sometimes omitted for simple databases with just a few users and tables.

True or False: Analysis considers implementation issues related to a specific database system.

- False - Analysis documents database requirements, without regard to implementation details for any database system.

True or False: Entities, relationships, and attributes always map directly to tables, foreign keys, and columns, respectively.

- False - In the logical design phase, entities, relationships, and attributes usually become tables, foreign keys, and columns. Sometimes, however, an entity splits into several tables, several entities merge into one table, and relationships and attributes become tables.

A table has: - columns A, B, C, D - candidate key (A, C, D) - primary key (A, C, D) - dependency D → B (in addition to dependencies on candidate keys) What is the normal form of the table?

- First Normal Form - the table has one candidate key, which is also a primary key - any table with a primary key is in FIRST nf - the non-key column B does not depend on the whole primary key...so not in SECOND normal form

True or False: Adding a FOREIGN KEY constraint to a table only affects inserting new rows into the table.

- Inserting and Updating rows are affected

Advantages of Views

- Protect sensitive data - Save complex queries - Save optimized queries

One-to-one Example State table w/ Primary Key StateCode - 1(0) City table w/ Primary Key CityCode In which table would the foreign key usually be placed? What is the name of the new foreign key? Are NULLs allowed in the foreign key column?

- State - CityCode - No: the minimum on the City side is 1 The foreign key usually goes in the table with fewer rows. The State table will usually have fewer rows than the City table, so the foreign key goes in the State table. The foreign key in the State table refers to CityCode, the primary key of the other table. NULLs are not allowed in the CityCode column of the State table, because the minimum on the City side is 1.

A table has: - columns A, B, C, D - candidate keys D and (A, C) - primary key (A, C) - dependency B → C (in addition to dependencies on candidate keys) What is the normal form of the table?

- THIRD - The table is in third normal form since non-key columns depend only on unique columns. - C is not a non-key column. - The table is not in Boyce-Codd normal form since C depends on the non-unique column B. - Primary keys must be minimal, so no column in a composite primary key can be unique. - Therefore, B is not unique.

A table has: - columns A, B, C, D - candidate key (A, B, D) - primary key (A, B, D) - dependency C → D (in addition to dependencies on candidate keys) What is the normal form of the table?

- THIRD - The table is in third normal form since non-key columns depend only on unique columns. - D is not a non-key column. - The table is not in Boyce-Codd normal form since D depends on the non-unique column C. - Primary keys must be minimal, so no column in a composite primary key can be unique. - Therefore, C is not unique.

Which two SQL data types can represent images or sounds?

- TINYBLOB - BINARY

CREATE TABLE Employee ( ID SMALLINT UNSIGNED, Name VARCHAR(60), BirthDate DATE, Salary DECIMAL(7,2) ); TRUE or FALSE: The ID column only holds non-negative integers?

- TRUE - The SMALLINT UNSIGNED data type specifies the ID column must be a number between 0 and 65,535. - If the company expects to hire more than 65,535 employees, a larger data type like MEDIUMINT should be used to create the Employee table.

Refer to the given SQL statement.INSERT INTO student VALUES('John','S',NULL),('Mary','S',NULL);What do the parentheses denote?

- The column values for two individual rows

Implementing one-one relationships

- The foreign key can go in the table on either side of the relationship. - Usually, the foreign key is placed in the table with fewer rows, to minimize the number of NULL values. - The foreign key refers to the primary key on the opposite side of the relationship. - The foreign key name is the primary key name with an optional prefix. The prefix is derived from the relationship name and clarifies the meaning of the foreign key.

Implementing many-one relationships

- The foreign key goes in the table on the 'many' side of the relationship. - The foreign key refers to the primary key on the 'one' side. - The foreign key name is the primary key name with an optional prefix. The prefix is derived from the relationship name and clarifies the meaning of the foreign key.

Implementing many-many relationships

- The new table contains two foreign keys, referring to the primary keys of the related tables. - The primary key of the new table is the composite of the two foreign keys. - The new table is dependent on the related tables, so primary key cascade and foreign key restrict rules are specified. - The new table name consists of the related table names with an optional qualifier in between. The qualifier is derived from the relationship name and clarifies the meaning of the table.

Violation of Third Normal Form

- a non-key column depends on a column that is not unique

What does ALTER TABLE allow?

- changing of columns

Logical Design

- converts the entity-relationship model into tables, columns, and keys for a particular database system

Analysis

- develops an entity-relationship model, capturing data requirements while ignoring implementation details

Relationship Maximum

- greatest number of instances of one entity that can relate to a single instance of another entity - a relationship has two maxima, one for each of the related entities - Maxima are usually specified as "one" or "many" - Ex: a department can have many employees; an employee can have one department

Ternary Relationships

- involves three different entity types - Ex: many-to-many-to-many ternary relationship among salespersons, customers, and products

Determine the maxima for each relationship. The correct answer may depend on business rules. Person-Owns-Vehicle

- many-many - A person can own many vehicles and, in most states, a vehicle can have many registered owners. Person-Owns-Vehicle is many-many.

Determine the maxima for each relationship. The correct answer may depend on business rules. Student-Takes-Course

- many-many - Each student can take many courses, and each course has many students, so Student-Takes-Course is many-many.

Determine the maxima for each relationship. The correct answer may depend on business rules. Person-Has-MailingAddress

- many-one - Several people can share the same mailing address. Assuming the business requires exactly one official mailing address for each person, Person-Has-MailingAddress is many-one.

Determine the maxima for each relationship. The correct answer may depend on business rules. Person-Has-Passport

- one-many - A person can hold passports from several different countries, and each passport belongs to one person. Person-Has-Passport is one-many.

Determine the maxima for each relationship. The correct answer may depend on business rules. City-IsCapitalOf-State

- one-one

Determine the minima for each relationship. The correct answer may depend on business rules. Flight-ArrivesAt-Airport

- one-one - All flights must arrive at an airport. Assuming the database does not track new or inactive airports, all airports have arriving flights.

Determine the minima for each relationship. The correct answer may depend on business rules. Person-Has-Passport

- one-zero - Every passport must belong to a person, but not all people have a passport.

How do you achieve THIRD normal form>

- remove the non-key column that depends on a candidate key column that causes redundancy - Ex: Azim Rafiq in "DepartmentChair" column depends on Math in the "DepartmentCode" column, which causes redundancy - If the non-key column, DepartmentChair, is removed, there is no longer redundancy

A table with a simple primary key must be in what normal form?

- second normal form or higher

Relationship Minimum

- the least number of instances of one entity that can relate to a single instance of another entity - a relationship has two minima, one for each of the related entities - Minima are usually specified as zero or one - Ex: Employee-WorksIn-Department relationship, a new department may have no employees, and each employee must be assigned to a department at all times. - Thus, Employee-WorksIn-Department minima are zero-one.

Third Normal Form

- when all non-key columns depend on the key, the whole key, and nothing but the key

First Normal Form

- when all non-key columns depend on the primary key - a table with a primary key is in first normal form - a table with no duplicate rows is in first normal form

Second Normal Form

- when all non-key columns depend on the whole primary key - cannot depend on part of a composite primary key - a table with a simple primary key is automatically in second normal form

Boyce-Codd Normal Form

- whenever column A depends on column B, then B is unique - same as Third Normal Form, except "non-key" is removed

Database Design

1. Analysis 2. Logical Design 3. Physical Design

Analysis Steps

1. Discover entities, relationships, and attributes 2. Determine cardinality 3. Distinguish independent and dependent entities 4. Create supertype and subtype entities

Logical Design Steps

1. Implement entities 2. Implement relationships 3. Implement attributes 4. Normalize tables

How does a row subquery differ from a table subquery?

A row subquery returns a single row of one or more values.

Which command line will modify the IDX_NAME index on the CUSTOMER table to include both NAME and TELEPHONE as part of the index?

ALTER INDEX IDX_NAME ON CUSTOMER(NAME, TELEPHONE);

What is the correct syntax to create an index to be built on the city field based on the database information?

ALTER TABLE 'Salesperson' ADD INDEX 'city_index' (`city`)

Add a constraint called MgrIdCheck to the existing Department table to ensure ManagerID is 2000 or above.

ALTER TABLE Department ADD CONSTRAINT MgrIdCheck CHECK (ManagerID >= 2000);

Drop the UNIQUE constraint called UniqueNameMgr from the existing Department table.

ALTER TABLE Department DROP INDEX UniqueNameMgr Or ALTER TABLE Department DROP CONSTRAINT UniqueNameMgr

SQL statement to add a column in an existing table?

ALTER TABLE TableName ADD ColumnName DataType;

SQL statement to change a column in an existing table?

ALTER TABLE TableName CHANGE CurrentColumnName NewColumnName NewDataType;

SQL statement to delete a column in an existing table?

ALTER TABLE TableName DROP ColumnName;

Which ALTER TABLE statement adds a foreign key constraint to a child table?

ALTER TABLE child ADD FOREIGN KEY (par_id) REFERENCES parent (par_id) ON DELETE CASCADE;

How does Table 2 affect the returned results from Table 1 in a left join?

All Table 1 results are returned, regardless of whether a match is found in Table 2.

Add a CHECK constraint called OrgCheck that limits OrgCode to 'ACH', 'PLT', or 'WAN'. CREATE TABLE Department ( Code TINYINT UNSIGNED, Name VARCHAR(20), OrgCode CHAR(3) ____________________________, PRIMARY KEY (Code) );

CONSTRAINT (OrgCode IN ('ACH', 'PLT', 'WAN'))

Add a UNIQUE constraint called UniqueNameMgr that ensures the Name and ManagerID combination are unique. CREATE TABLE Department ( Code TINYINT UNSIGNED, Name VARCHAR(20), ManagerID SMALLINT, OrgCode CHAR(3), _______________________________________________, PRIMARY KEY (Code) );

CONSTRAINT UniqueNameMgr UNIQUE (Name, ManagerID)

What is the difference between COUNT(*) and COUNT( col_name) for specified column names?

COUNT(*) counts every row selected, and COUNT(col_name) counts only non-null values.

Which command creates a database only if it does not already exist?

CREATE DATABASE IF NOT EXIST db_name;

Which method creates an empty copy of a table and then populates it from the original table?

CREATE TABLE ... LIKE followed by INSERT INTO ... SELECT;

Which syntax is the correct way to use the DROP INDEX command to drop a primary key from a table?

DROP INDEX `PRIMARY` ON tbl_name ;

Which statement should be used so that it assigns a foreign key to the customer name?

FOREIGN KEY ( customer_name ) REFERENCES table_name ( customer_name )

Refer to the given SQL statement. SELECT PRODNUM, SUM(QUANTITY) FROM SALESPERSON Which line, when added to the end of the statement, returns the total number of each kind of product by product number?

GROUP BY PRODNUM;

Which clause in a CREATE TABLE statement creates an index on a field?

INDEX index_name (index_columns)

Refer to the given SQL statement. SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2 What does the LEFT JOIN statement do?

It selects all of the rows in Table 1 and the matching rows in Table 2.

Which command should be added to the end to make the event_id attribute the primary key within a CREATE TABLE statement?

PRIMARY KEY (event_id));

What is the name of the special internal database where the query optimizer finds information?

Relational Catalog

Which SQL statement tallies the number of different cities in which record companies have been founded?

SELECT COUNT(DISTINCT city) FROM recordcompany;

Write a statement that SELECTs the CountryCode and sum of Poplulation from the City table, and groups by the CountryCode

SELECT CountryCode, SUM(Population) FROM City GROUP BY CountryCode

Write a statement that gets the following output from Table Auto: Ford 1 Honda 2 Toyota 3 Volkswagen 1

SELECT Make, Count(*) FROM Auto GROUP BY Make ORDER BY Make;

A database administrator of a movie rental company needs to compile a list of movies released each year for a marketing campaign. Which SQL command will accomplish this?

SELECT YEAR, TITLE FROM MOVIE GROUP BY YEAR;

Write a statement that gets the following output from Table Auto: 2014 8800 2015 16400 2016 12675

SELECT Year, AVG(Price) FROM Auto GROUP BY Year ORDER BY Year;

Write a statement that gets the following output from Table Auto for cars that only have a price over 15000: 2015 crossover 15900 2015 suv 16900 2016 crossover 17900

SELECT Year, Type, MAX(Price) FROM Auto GROUP BY Year, Type HAVING MAX(Price) > 15000 ORDER BY Year, MAX(Price);

Which task does ORDER BY perform by default?

Sorting rows in ascending order

In some diagrams, 'many' is depicted with ______ notation.

crow's feet

A group of related entities is often called a/an ______ .

subject area

In some models, dependent entities are called ______ entities.

weak