Cop4710 Midterm

Front 1

What is a database?

Back 1

A collection of data items related to some enterprise.

Front 2

In the Relational Model, data is stored in

Back 2

Correct. The Relational Model is a logical model, in which the details of the hardware (disk and memory) and the operating system (the file system) are irrelevant. Data in a Relational Database is stored in tables.

Front 3

The word "Relational" refers to __________, while a "Relation" is a __________.

Back 3

set-based mathematics; a table

Front 4

Physical data independence means

Back 4

Data is isolated from the underlying hardware by high level abstractions.

Physical Data Independence means that applications are insulated from the underlying hardware and software of the host system. Applications see data as residing in good, clean tables which are mathematical constructs at a high level of abstraction. The underlying hardware and software can change, but the data always looks the same.

Front 5

If a table contains two attributes that are each unique on their own, and either one could serve as a primary key, these attributes are called

Back 5

Candidate keys

Candidate keys are columns in a table that each have unique values within its column; either one could serve as a primary key. Examples are EmployeeId and SSN in the Employee table. We have to select one of these candidate keys as the Primary Key and use it consistently for identifying rows.Both the primary and candidate keys can be used for linking to Foreign Keys. This may sound in contradiction to "use it consistently for identifying rows", but in certain contexts it may be more appropriate to use a candidate key instead of the primary key. An example is when our Employee table is used for federal tax reporting, it might be appropriate to link employee rows to tax rows by SSN instead of EmployeeId.

Front 6

Every table must have a

Back 6

Primary Key

Front 7

Rows in a relational table need not be unique; there can be duplicate rows in a table.

Back 7

FALSE

Front 8

The mathematically-based framework of Codd's Relational Model is called a "__________ Data Model".

The data-structure-based framework of Bachman's Network Database is called a "__________ Data Model".

Back 8

Conceptual, Physical

Codd's mathematically-based Relational Model is a "Conceptual Data Model" because it represents data in high level constructs, and hides the physical details. It has a high level of abstraction.Bachman's data-structure-based Network Database is a "Physical Data Model" because it involves itself with data on the physical level of data structures at the machine level. It has a low level of abstraction.

Front 9

When a table has more than one attribute that could each serve as the table's primary key (for example EmployeeNum and SSN), we call these __________ Keys.

Since we want to consistently use only one key for linking tables together, we select one of the table's candidate keys as the __________ Key.

The primary key column of a parent table is placed in a child table to establish a means of linking rows that belong together in the two tables, is called a __________ Key.

Back 9

Candidate, Primary, Foreign

Candidate Key: If a table has more than one attribute that could each serve as the table's primary key (for example EmployeeNum and SSN), we call these Candidate Keys.

Primary Key: Since we want to consistently use only one key for linking tables together, we select the most appropriate of the table's candidate keys as the Primary Key.

Foreign Key: The linking mechanism between tables. The primary key column of a parent table is placed in a child table to establish a link between rows that belong together in the two tables.

Front 10

A Foreign Key column is not a key itself. Each row in the child table contains the same value in its Foreign Key attribute as in the Primary Key attribute of its Parent row. As a result, rows in the two tables are linked together. This works because:

Back 10

The rows in the two tables have a common value.

Front 11

Which of the following is not part of Peter Chen's Entity Relationship model?

Back 11

Primary Keys

Front 12

In a one-to-many (1:M) relationship, the entity on the "many" side is the

Back 12

child

Front 13

An ID-Dependent Entity is

Back 13

An entity whose identifier (key) contains (is partially composed of) the identifier of another entity.

An ID-Dependent entity is an entity whose key contains (is partially composed of) the key of another entity, for example, a room of a building. Say we have a Building table representing buildings on campus, and a Room table for rooms in each building. Building's identifier is BuildingNum, and Room's identifier is the composite BuildingNum/RoomNum.To identify a Room we need to know which building it's in -- just knowing the room number isn't enough. For example, there are many rooms on campus with the number 101. We need to know which building the room belongs to before we can identify the specific room. So Room is an ID-Dependent entity because Room's identifier includes the identifier of its "parent" entity, Building.Another example is Street/Number, such as "234 Elm Street".A relationship which results in an ID-Dependent entity is called an Identifying Relationship. All ID-Dependent entities are weak entities.

Front 14

"One-to-one", "one-to-many", and "many-to-many" (or 1:1, 1:M, and M:M) are examples of

Back 14

cardinality ratios

Front 15

An advantage in expressing a data model as an ER diagram is that ER diagrams are very uniform in their notation and all database designers use identical standards in producing ER diagrams.

Back 15

False

Front 16

Mandatory Participation means

Back 16

The minimum cardinality of a relationship is one or more.

Front 17

A relationship name in an ER diagram can be different depending on the direction you read the relationship.

Back 17

True

Front 18

We have to determine foreign keys when building an ER diagram.

Back 18

False

Front 19

A Composite Key is

Back 19

An Key that's made up of more than one attribute.

Sometimes there isn't a single attribute that identifies an entity, so we use a combination of attributes.

An example is BldgNum/RoomNum in the Room entity. A key made up of more than one attribute is called a Composite Key.

Front 20

A mandatory participation constraint (meaning there is a required participation between one entity and another) always indicates that an entity is weak.

Back 20

False

An entity can have mandatory participation and still be a strong entity. For example, you plan a Trip that includes Reservations. You establish a rule that you can't make a Reservation outside the bounds of a Trip. If the Trip is canceled, the reservations you made will still exist and must be dealt with. This shows that Reservation is a strong entity even though it has mandatory participation with Trip.Contrast this to a weak entity such as a building's rooms. If a building is demolished, all the building's rooms be gone, too.

Front 21

In SQL, how do you select a column named "FirstName" from a table named "Employee"?

Back 21

SELECT FirstName FROM Employee

Front 22

In SQL, how do you select all the columns from a table named "Employee"?

Back 22

SELECT * FROM Employee

Front 23

In SQL, how do you select all the rows from a table named "Employee" where the value of the column "FirstName" is "John"?

Back 23

SELECT * FROM Employee WHERE FirstName = 'John'

Front 24

What SQL statement is used to return only different values from a table, eliminating duplicates?

Back 24

SELECT DISTINCT

Front 25

With SQL, how can you return all the rows from a table named "Employee" sorted in descending order by FirstName?

Back 25

SELECT * FROM Employee ORDER BY FirstName DESC

Front 26

In SQL, how do you list all the rows of the Employee table and Dependent table that match on the EmployeeId attribute?

Back 26

SELECT *FROM Employee JOIN DependentON Employee.EmployeeId = Dependent.EmployeeId

Front 27

What query would we use to select all the employees from the Employee table whose first name is the same as his last name?

Back 27

SELECT *FROM EmployeeWHERE FirstName = LastName

Front 28

What is the difference between an INNER JOIN and a LEFT JOIN? Remember that "JOIN" is the same as "INNER JOIN". An example is:

SELECT *

FROM Employee INNER JOIN Dependent

ON EmployeeId = DependentID

SELECT *

FROM Employee LEFT JOIN Dependent

ON EmployeeId = DependentID

Back 28

The INNER JOIN returns only the rows in which the join columns match.The LEFT JOIN returns all the rows of the left table, along with rows of the right table in which the join columns match.

The INNER JOIN (also called a JOIN) returns only the rows in which the join columns match.

This query:

SELECT *

FROM Employee INNER JOIN FamilyMember

ON EmployeeId = FamilyMemberID

gives us only those Employees who have FamilyMembers, and only the FamilyMembers who have Employees. Employees with no FamilyMember aren't listed, and FamilyMembers with no Employees aren't listed either.

=====

The LEFT JOIN (also called the LEFT OUTER JOIN) gives us all the rows from the table on the left, and any rows in which the join columns match from the table on the right. This query:

SELECT *

FROM Employee LEFT JOIN FamilyMember

ON EmployeeId = FamilyMemberID

gives us all the Employees, and also lists their FamilyMembers if they have any. FamilyMembers who have no Employee aren't listed.=====The FULL JOIN (also called a FULL OUTER JOIN) gives us all the rows from both tables, with the rows listed together if the join columns match.

Front 29

In SQL, how do you add a new row to the table named 'Employee'?

Back 29

INSERT INTO Employee (empNo, fName, lName)VALUES (245, 'Bill', 'Jones');

Front 30

In SQL, how do you change the value of an attribute in an existing row?

Back 30

UPDATE Employee

SET DeptNum = 200

WHERE EmpNum = 493;

Front 31

In SQL, how do you delete a row from a table?

Back 31

DELETE FROM Employee

WHERE EmpNum = 449;

Front 32

The primary purpose of the Subtype/Supertype relationship is to

Back 32

All these answers might apply to the Subtype/Supertype pattern. However, the primary reason to use the subtype/supertype is to eliminate type-inappropriate nulls.By type-inappropriate we mean that a table could model different variations of the same object (commercial/private drivers licenses, commercial/sport fishermen, male/female medical patients) that have most of their attributes in common but a significant number of different attributes. When the table contains an instance of one variation, the attributes of the other variation will be null. By separating the attributes that pertain to different variations into separate tables, type-inappropriate null values are avoided.

Front 33

To implement a Many-to-Many relationship, you do this:

Back 33

Add an intersection table between the two tables. The intersection table will contain a foreign key to each of the two tables being linked.

You can't directly implement a many-to-many relationship between two tables in a relational database (though you can in an ER diagram). You build an intersection table that stands between the two tables. The intersection table contains the key of each of the two tables being linked. (This means the intersection contains a foreign key pointing to each of the two tables).

This implements the many-to-many relationship. The intersection table has one row for each pair of rows connected in the many-to-many relationship.

The Association data pattern is similar but adds extra attributes for data about each instance of the relationship.

Front 34

The relational model does not directly support many-to-many relationships.

Back 34

TRUE

Front 35

The hallmark of an ID-dependent entity is that it

Back 35

has the identifier (key) of another entity as part of its own identifier

Front 36

The Association data pattern is similar to a ______ relationship, but with the addition of extra attributes in the ______ table which describe the relationship.

Back 36

many-to-many, intersection

The Association data pattern is a many-to-many relationship with additional attributes. It links two entities together like a regular many-to-many relationship, and adds extra attributes to describes the interaction between the two entities.

For example, in a boat rental business, a Customer may have rented many Boats, and a Boat may have been rented by many Customers. This is a many-to-many relationship between Customer and Boat. This is implemented by an intersection table, each row of which contains the primary keys of both Customer and Boat.

If we add additional attributes to the intersection table for rental date and amount charged, then we have the details of the rental transaction.

The Association pattern is a common way to record interactions between entities.

Front 37

Your job is to produce a parts table for a lawn mower. The lawn mower has several assemblies: the engine, the deck, and the handle. The engine is composed of the block and the carburetor. The carburetor is composed of the body, the butterfly valve and the float chamber. And on it goes.You draw the pieces and see that it makes a tree, with the lawn mower at the top (the root) of the tree; the engine, etc. on the next level down; the carburetor, etc. on the level below that; and so on.What data pattern do you use to model this?

Back 37

One-to-Many Recursive pattern

Front 38

The Recursive Pattern can model a

Back 38

The 1:1 Recursive pattern can model a list in the same way that a linked list can. Each node has at most one parent and one child.

The 1:M Recursive pattern can model a tree. Each node has at most one parent, but can have multiple children.

The M:M Recursive pattern can model a network. Each node can have multiple parents and multiple children.

The 1:1 and 1:M Recursive patterns are implemented by using a single table and adding a foreign key. The foreign key links the table to its own primary key. One entity in the table is linked to another entity in the same table. For the 1:1 Recursive pattern, give the foreign key the Unique constraint. For the 1:M Recursive pattern, omit the Unique constraint.

The M:M Recursive pattern uses two tables, the original table, which contains the nodes of the network, plus an intersection table. When implementing this pattern, the original table doesn't contain a foreign key. Instead, the intersection table contains two keys, one for each of the nodes being connected.

Front 39

Is a foreign key always the key of some other table? (True=Yes; False=No)

Back 39

FALSE

Front 40

If we want to record multiple e-mail addresses for each person in our Contact table, which data pattern would we use?

Back 40

Multivalued attribute pattern

Front 41

Referential Integrity means:

Back 41

assuring that the connections between tables remain valid

Front 42

What advantage is there to using a surrogate key?

Back 42

A surrogate key doesn't represent a fact about an entity. It's a pure linking construct. If a natural key were used in a table, the fact it represents may change. This would violate the unchangability of primary keys and weaken referential integrity.

A natural key may turn out to be non-unique. Surrogate keys are always unique.

Since we can choose the data type of a surrogate key, we can choose the type that's most efficient for keys.

Front 43

Aggregate functions work with

Back 43

An aggregate function produces a result from a group of rows. The aggregate functions are:

AVG( )

COUNT( )

MAX( )

MIN( )

SUM( )

Front 44

The WHERE clause filters _____ , while the HAVING clause filters _____ .

Back 44

individual rows, groups

Front 45

WHERE and HAVING are similar because both are

Back 45

filters

Front 46

A correlated subquery is executed each time

Back 46

the outer query reads a row from a table

Front 47

If a SQL statement contains a GROUP BY clause, then the SELECT clause can contain only

Back 47

the GROUP BY attribute plus aggregate functions

Front 48

In a SQL query containing a GROUP BY clause, filtering of individual rows occurs before filtering and sorting of groups.

Back 48

TRUE

The order in which things in the statement occur is this:

1. The WHERE clause is applied first to filter the individual rows.

2. Then the GROUP BY clause assembles the rows into groups.

3. Then the HAVING clause filters the groups.

4. Finally, the ORDER BY clause sorts the groups.

Front 49

A subquery can appear in a SQL statement wherever a _____ can appear.

Back 49

set of values

Front 50

A correlated subquery can often be replaced by a

Back 50

join

Front 51

Referential Integrity means

Back 51

a foreign key value in a child table always has a matching primary key value in a parent table.

Referential Integrity means making sure that all the foreign key values in a child table always point to valid primary key values in the parent table. That means there shouldn't be a value in the foreign key attribute of a child row without that same value appearing in the primary key attribute of a parent row.

A child row without a parent is an orphan, and is a violation of referential integrity.

Front 52

a "Cascading Delete" is

Back 52

Deleting a row in a parent table automatically deletes any corresponding rows linked by a foreign key in the child table, thereby preventing orphans.

Front 53

The thing about NULL values that throws SQL users off is that Boolean expressions can evaluate into three results -- True, False, and Unknown. This is called Three Value Logic. NULL represents the Unknown value.

If you search for NULL values using this statement:

SELECT *

FROM SomeTable

WHERE SomeAttribute = NULL;

why won't it ever return any rows? (Hint: What does the WHERE clause evaluate to?)

Back 53

Because you are using NULL in a boolean expression. NULL is not equal to anything, even NULL. You must use IS NULL to return any rows.