c170

Attribute type vs attribute instances

#Attribute type is a set of values. For e.g: all employee salaries. # attribute instances is an individual value. For e.g: the salary $35,000.

Entity type vs. Entity instance

#Entity type is a set of things For e.g: all employees in a company. #Entity instance is an individual thing. For e.g: the employee Sam Snead.

Relationship Types vs. Relationship Instances

#Relationship type is a statement about entity types. For e.g: employee-manages-department. #Relationship instance is a statement about entity instances. For e.g: Maria Rodriguez manages Sales.

Analysis

- develops an entity-relationship model, capturing data requirements while ignoring implementation details. -important for complex databases with many users. -analysis documents database requirements, without considering implementation details.

Structural rule

-Tables are normalized — exactly one value exists in each cell -no duplicate column names allowed. Same column name can appear in a different table. -no duplicate rows allowed in all columns.

Referring to the animation above, how is the "schedules' relationship read? 1) Flight-Schedules-Airline 2) Airline-Schedules-Flight 3) Flight-ScheduledBy-Airline

2) Airline-Schedules-Flight Note: Relationships are read in the direction of the verb. 'Schedules' starts at 'airline' on the ER diagram, so 'airline' is read first.

An entity-relationship model is completely described by : 1) An ER diagram 2) A glossary 3) Both an ER diagram and a glossary

3) Both an ER diagram and glossary Note: The ER diagram provides an intuitive overview of the model, and the glossary provides full detail. Both ER diagram and glossary, together, describe an entity-relationship model.

ORDER BY clause

A SELECT statement selects rows from a table with no guarantee the data will come back in a certain order. The ORDER BY clause orders selected rows by one or more columns in ascending (alphabetic or increasing) order. The DESC keyword with the ORDER BY clause orders rows in descending order. (Refer to the picture)

Add a constraint called MgrldCheck to the existing Department table to ensure ManagerID is 2000 or above. Fill in the blank. ALTER TABLE Department ____________________ ManagerID >= 20000;

ADD CONSTRAINT MgrldCheck CHECK Note: The ADD CONSTRAINT keywords add the new CHECK constraint to Department.

A database administrator needs to modify an index on the CUSTOMER table called IDX_NAME because there are multiple customers with the same name. It has been determined that it would be desirable to modify the index to also include the telephone number. 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);

SUM

Aggregate function which returns the total of selected values.

Match the like statement with the matching language: (refer to the picture) LIKE '%r%n'

Ans: Arawakan and Caribbean (note: Both languages have any number of characters before "r" and any number of characters before "n".)

LIKE '%cha'

Ans: Chibcha (note: Only Chibcha ends with "cha".)

LIKE BINARY '%E%'

Ans: English (note: BINARY makes the pattern matching case-sensitive. Only English has a capital "E".)

LIKE '%m_o%'

Ans: Maori (note: Only Maori has an "m" followed by a single character and "o".)

Based on the data in the Country table, select the properties satisfied by the composite column (ContinentPopRank, Area). Refer to the diagram. Can (ContinentPopRank, Area) be a primary key of the Country table? Yes or No?

Ans: Unique, Minimal, No Note: Although ContinentPopRank and Area are not unique by themselves. The combination of these two columns uniquely identifies each row.

Binary

BLOB

How can driver's license information be added to the Employee table? 1) Driver's licenses cannot be added, since the Employee table already has an ID column. 2) By creating another column named ID. 3) By creating another column with a new name, such as ID2 or DriverLicense.

By creating another column with a new name, such as ID2 or DriverLicense. (note: A table may have several columns with similar information, as long as the column names are different.)

If the column in a table called "code" is always going to be three letters long, what is the best data type for this column?

CHAR (3)

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

CONSTRAINT OrgCHECK CHECK Note: The constraint name follows CONSTRAINT, then the CHECK constraint.

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

CONSTRAINT UniqueNameMgr UNIQUE (Name, MangerID) Note: The table-level constraint appears on a line by itself because the constraint applies to more than one column.

Which rows appear in the result of the following query? Refer to the diagram. SELCT Continent, COUNT (*) FROM Country GROUP BY Continent HAVING COUNT (*) >= 3;

Europe, 4 GROUP BY Continent: Forms groups based on the Continent column. Having COUNT (*) >= 3: Selects only groups that have a row count >= 3. The Europe, 4 group has >=3 rows.

An entity-relationship model is developed for all database design projects. TRUE or FALSE?

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.

Entities, relationships, and attributes always map directly to tables, foreign keys and columns, respectively. TRUE or FALSE?

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.

Analysis considers details of a specific database. TRUE or FALSE?

FALSE. -analysis documents database requirements, without considering implementation details.

Adding a FOREIGN KEY constraint to a table only affects inserting new rows into the table. TRUE or FALSE?

FALSE. Inserting and updating rows are affected. Ex: Updating the ManagerID 2538 to 9999 is rejected if Employee ID 9999 does not exist.

If the column in a table called "Population" can't be negative and population value can't exceed 1,500,000,000, what is the best data type for this column? Refer to the diagram.

INTEGER UNSIGNED or INT UNSIGNED

In this entity-relationship diagram, many salespersons are shown as working in at least one office.How is the relationship between salespersons and offices represented in the diagram?

It is linked through the office ID.

In the Bonus column of the Lisa Ellison row, what does the zero represent? 1) Lisa Ellison's bonus is unknown. 2) Lisa Ellison is not eligible for a bonus. 3) Lisa Ellison has earned no bonus.

Lisa Ellison has earned no bonus. (note: Zero is a valid bonus amount. Zero is not the same as unknown or inapplicable data.)

Based on the data in the Country table, select the properties satisfied by the column IndependenceYear. Unique Not Null Minimal Can IndependenceYear be a primary key of the Country table? YES or NO?

Minimal, No Note: #IndependenceYear is not unique because NULL is repeated. #IndependenceYear may contain NULL, since NULL values appear in the #IndependenceYear column. IndependenceYear is minimal, since IndependenceYear is a single column. #Primary keys must be unique and not NULL. Composite primary keys must be minimal. IndependenceYear is not unique and has NULL values, therefore cannot be a primary key.

Purpose of ON DELETE SET NULL?

ON DELETE SET NULL sets the foreign key to NULL when the matching primary key is deleted.

Purpose of ON UPDATE CASCADE?

ON UPDATE CASCADE updates the foreign key to the same value used to update the primary key. -ON UPDATE only applies to updating the primary key, not the foreign key. 9999 is an invalid foreign key since 9999 does not exist in Employee ID. The database does not allow invalid foreign keys.

Spatial

POINT

The Book table has the following columns: ID - integer, primary key X - integer Y - integer Complete the SELECT statement to computer the absolute value of (columns X), to the power of 3. Fill in the blank. SELECT __________________________ FROM Book;

POW (ABS(X), 3) ABS (X): returns the absolute value of X. POW (ABS(X), 3): returns ABS (X) to the power of 3.

When SELECT MAX (Salary + Bonus) from the compensation table in the picture,

Salary + bonus is NULL for Lisa Ellison and Jiho Chen, so MAX ignores these rows. MAX selects the largest amount from the remaining two rows. So, 90000 + 3000. Ans: 98000

Which task does ORDER BY perform by default?

Sorting rows in ascending order

A FOREIGN KEY constraint must REFERENCE a primary key. TRUE or FALSE?

TRUE. Foreign keys must refer to a primary key in the same table or another table.

IN operator

The IN operator is used in a WHERE clause to determine if a value matches one of several values. The SELECT statement in the figure below uses the IN operator to select only rows where the Language column has a Dutch, Kongo, or Albaniana value.

LIKE operator

The LIKE operator, when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _.

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

If the Department column is designated NOT NULL, what happens when a user attempts to insert a new employee without a department value? 1) The database accepts the insert and stores a blank string as the Department name. 2) The database accepts the insert and saves NULL in the column. 3) The database rejects the insert.

The database rejects the insert. (note: A database does not accept an insert with missing Department data. The insert statement fails.)

In this entity-relationship diagram, Salesperson 361 is related to two customers. The table follows the restrict delete rule.What happens if someone tries to delete Salesperson 361 from the customer records?

The delete is prohibited.

What does the principle of data independence state? 1) The performance of a query is not related to the physical organization of data. 2) Data in each row is independent of data in all other rows. 3) The result of a database query is not affected by the physical organization of data on storage devices.

The result of a database query is not affected by the physical organization of data on storage devices.

NOT NULL

To prohibit NULL, a column can be designated NOT NULL in SQL. The database rejects any attempt to insert a row with missing data in a NOT NULL column.

Based on the data in the Country table, select the properties satisfied by the composite column (Continent, ContinentPopRank). Unique Not Null Minimal Can (Continent, ContinentPopRank) be a primary key of the Country table? YES or NO?

Unique, Not NULL, Minimal, Yes Note: #(Continent, ContinentPopRank) is unique. Although neither Continent nor ContinentPopRank is unique, the combination (Continent, ContinentPopRank) uniquely identifies each row. #(Continent, ContinentPopRank) is not NULL, since no NULL values appear in either Continent or ContinentPopRank. #(Continent, ContinentPopRank) is minimal, since both Continent and ContinentPopRank are necessary for uniqueness. #Primary keys must be unique and not NULL. Composite primary keys must be minimal. (Continent, ContinentPopRank) is unique, not NULL, and minimal, therefore is a valid primary key.

Based on the data in the Country table, select the properties satisfied by the composite column (Continent, ISOCode2). Unique Not NULL Minimal Can (Continent, ISOCode2) be a primary key of the Country table? YES or NO?

Unique, Not Null, No. Note: #(Continent, ISOCode2) is unique, because the column ISOCode2 is unique. #(Continent, ISOCode2) is not minimal. Since ISOCode2 is unique, Continent is not necessary for uniqueness. #Primary keys must be unique and not NULL. Composite primary keys must be minimal. (Continent, ISOCode2) is not minimal and therefore cannot be a primary key.

What does a NULL in the Name column present? 1) Unknown 2) Inapplicable 3) Either unknown or inapplicable

Unknown. (note: Every employee has a name, so NULL indicates the name exists but has not been entered. The name is unknown to the database.)

DROP TABLE with foreign key constraints

When a table with a foreign key constraint references a table's primary key, the table with primary key cannot be deleted with a DROP TABLE statement unless the table with the foreign key constraint is deleted first. Ex: The Employee table cannot be deleted without first deleting the Department table.

Document

XML

Is RESTRICT applied by default?

YES. RESTRICT rejects an insert, update, or delete that violates referential integrity. RESTRICT is applied by default when no action is specified.

CHAR

a fixed string of characters

VARCHAR

a string of variable length up to a specified maximum size.

AVG

aggregate function that returns the average of selected values.

entity and attribute

attribute is the property that describes the entity.

Which columns can be compared in a join? a) Only primary and foreign key columns. b) Only columns with comparable data types. c) Any columns.

b) Only columns with comparable data types

What is returned by the query? (refer to the picture) SELECT Name FROM Country WHERE Continent IN ('Asia', 'Europe', 'South America'); a) No results b) Afghanistan, Albania, Aruba c) Afghanistan, Albania, Andorra

c) Afghanistan, Albania, Andorra (note: Afghanistan is in Asia, Albania and Andorra are in Europe, and no countries are in South America.)

CHAR

character

cross-join

combines two tables without comparing columns. (DOESN'T use WHERE or ON clause.)

BLOB

common binary data type that stores data in binary form (0s and 1s)

FLOAT

common decimal binary type that represents numbers with fractional values.

non-equijoin

compares columns with an operator other than =, such as <, >

Document data types

contain textual data in a structured format such as XML or JSON

Most joins are equijoin or non-equijoin?

equijoin

TIMESTAMP

includes time zone.

Decimal data types

numbers with fractional values. FLOAT and DECIMAL

Time data types

represent date, time or both. For e.g: DATE, TIME, DATETIME, and TIMESTAMP

SELECT DATE ('2013-03-25 22:11:45');

returns '2013-03-25'

SELECT TIME ('2013-03-25 22:11:45');

returns '22:11:45'

SELECT CURDATE();

returns current date, time or date and time in 'YYYY-MM-DD'

DAY (d) MONTH (d) YEAR (d)

returns the day or month or year from date d

HOUR (t) MINUTE (t) SECOND (t)

returns the hour, minute or second from time t

MAX

returns the largest selected value.

MIN

returns the smallest selected value. (note: SUM, AVG, MIN, and MAX ignore NULL values.)

full join

selects all left and right table rows, regardless of match.

left join

selects all left table rows, but only matching right table rows.

right join

selects all right table rows, but only matching left table rows.

inner join

selects only matching left and right table rows.

SET NULL

sets an invalid foreign key value to NULL

SET DEFAULT

sets invalid foreign keys to a default primary key value, specified in SQL.

NULL

special value that represents missing data (unknown or inapplicable.) Note: zero is not same as NULL since it is still applicable.

Binary data types

store data exactly as the data appears in memory or computer files, bit for bit Common binary data types: BLOB, BINARY, VARBINARY, and IMAGE

Spatial data types

store geometric information, such as lines, polygons, and map coordinates.

alias

the AS keyword follows a column or table name to create an alias.

Some columns should never contain a NULL. What does this mean?

the ID column identifies employees and should always have a valid integer value and therefore NULL cannot be present here.