D426 Reading Tables

What is returned?

SELECT Name
FROM Compensation
WHERE NOT(Salary > 30000 AND Bonus > 1000);

Nothing is returned.

For Lisa Ellison, 115000 > 30000 is TRUE but NULL > 1000 is NULL. NOT(TRUE AND NULL) evaluates to NOT(NULL). Since NOT(NULL) is NULL, Lisa Ellison is not selected.

Not to be confused with NOT NULL which returns values that are not NULL.

What do the double lines mean? What do the double/single outline squares mean? What do the underlined/dotted underlined attributes mean? What are the PKs/Composite PKs?

What do the squares, circles and diamonds mean? What do double diamonds mean? What is the partial key? What are the strong/weak entities?

Double lines mean the weak entity cannot exist without the strong entity.

Squares are entities, circles are attributes, diamonds are relationships.

Double squares are weak entities, single outline squares are strong entities.

Double diamonds are where the strong entity’s PK is part of the weak entity’s PK.

Underlined attributes are or are part of PKs, dotted underlined attributes are partial keys.

SecNum is the partial key. It uniquely identifies a section within a course.

PKs: CourseID, StudentID Composite PK: (CourseID, SecNum, Semester, Year)

Weak Entities: Section, Enrollment Strong Entities: Course, Student

What is returned?

SELECT DISTINCT Language
FROM CountryLanguage
WHERE IsOfficial = 'F';

Spanish and Balochi.

The DISTINCT keyword is used with a SELECT statement to return only unique or 'distinct' values

What is returned?

SELECT Language
FROM CountryLanguage
ORDER BY Language;

Fang, Mbete, Woleai, Yap

ORDER BY will list in ascending order. ORDER BY DESC will list in descending order.

What is returned?

SELECT CountryCode, Language
FROM CountryLanguage
ORDER BY CountryCode, Language;

FSM Woleai
FSM Yap
GAB Fang
GAB Mbete

ORDER BY goes in ascending order, starting with the CountryCode (FSM comes before GAB) and then the Language (Woleai comes before Yap, and etc.)

What form is this table in?

A. Unnormalized

B. 1NF

C. 2NF

D. 3NF

B. This table is still in 1NF because of partial dependencies. Item Name and Item Price depend only on part of the key.

What form is this table in?

A. Unnormalized

B. 1NF

C. 2NF

D. 3NF

C. The table is in 2NF but has a transitive dependency on Coordinator ID that must be resolved for 3NF.

What form is this table in?

A. Unnormalized

B. 1NF

C. 2NF

D. 3NF

A. This table has not been normalized because there are repeating groups and a multivalued field.

What form is this table in?

A. Unnormalized

B. 1NF

C. 2NF

D. 3NF

A. Because this table contains multiple non-unique rows it does not satisfy the criteria for any normal forms.

What is the modality of appointment?

A. At least one

B. Dependent on the quantity

C. Two

D. Two or more

A. Modality is a description of the minimum number of values on one side of a relationship.

Agents at the NWREA are assigned as a mentor to one other agent. Each NWREA mentor can only work with one agent. Each agent is only mentored by one PNREA agent. Being mentored is required, being a mentor is not. Which kind of relationship does the diagram depict?

A. Unary one-to-many

B. Binary many-to-many

C. Unary one-to-one

D. Binary one-to-many

C. Because each mentor can only have one agent and each agent only one mentor, the relationship is Unary one-to-one.

When a table points at itself, it is unary.

Agents at the NWREA may be assigned as a mentor to zero or more other agents. Each agent being mentored is mentored by only one NWREA agent. Which relationship does the diagram depict?

A. Unary one-to-many

B. Binary many-to-many

C. Unary many-to-many

D. Binary one-to-many

A. A single table makes this a Unary relationship, and the outer icons indicate a max of 1 on one side and many on the other.

When a table points at itself, it is unary.

You are creating a relational database to store info about instructors and the courses that each instructor teaches. Each course is taught by a single instructor. You have created an Instructor table and a Course table. Create a relationship between the Instructor table and the Course table. Keep duplicate data to a minimum. How would you do this?

A. Create a new column in the Instructor table.

B. Create new columns in the Instructors table for each course taught.

C. Create a new table that includes two columns.

D. Create a new column in the Course table

D. Create a new column in the Course table.

This is a One to Many binary relationship. To connect these two, add a foreign key on the MANY side. That means adding InstructorID as a foreign key in Course referencing Instructor InstructorID primary key.

This table has these characteristics:

• Each student has a unique StudentID that references the Students table.

• A specific test can be taken more than once.

• Each instructor has a unique InstructorID that references the Instructors table

• Each student may take multiple tests on the same date.

• Each student may take tests on multiple dates.

What column or columns should you use for the primary key?

A. StudentID, Date

B. StudentID, Test, Date, InstructorID

C. StudentID, Test, and Date

D. Test

C. Since StudentID and InstructorID are foreign keys and there are no other unique values, it is necessary to combine fields to form a Composite Primary Key.

❌Score and InstructorID allow Nulls, that means they can’t be part of a composite key or primary key.

❌Students can take multiple tests on one date, so StudentID and Date is not sufficient for uniqueness.

Each teacher is associated with exactly one campus. The address in the table is the address for the campus where the teacher teaches.

What change is needed to normalize the database to the third normal form (3NF)?

A. Combine the FirstName and LastName columns into a single column.

B. Combine the Campus, Address, City, State, Country, and PostalCode columns into one column.

C. Create a separate table for campus address information. Use the Campus as the primary key. Add a TeacherID column to the table and relate it to the Teacher ID column in the Teachers table.

D. Create a separate table for campus address information. Use Campus as the primary key for the table. Create a foreign key in the Teachers table that relates teacher to the campus.

D. In the Teachers table, the address fields are related to the Campus field instead of the Primary Key TeacherID. Moving those fields to a new table using Campus as a primary key results in 3rd normal form.

Which of the following is a partial dependency?

A. PROJ_NUM --> PROJ_NAME

B. PROJ_NAME --> HOURS

C. PROJ_NUM, EMP_NUM --> HOURS

D. PROJ_NUM, EMP_NUM --> PROJ_NAME

A. PROJ_NUM --> PROJ_NAME shows a non-key field dependent on only one part of a composite primary key, resulting in a partial dependency.

What is the modality of Volunteer?

A. Two or more
B. Two
C. Dependent on registration date
D. At least one

D

What is the correct way to read the modality on the right side of the association?

A. A maximum of one activity
B. A maximum of zero activities
C. A minimum of zero activities
D. A maximum of many activities

C. Modality is a description of the minimum number of values on one side of a relationship.

What is the E-R Diagram about an online pet food vendor showing?

A. An Invoice Line can include many Items.
B. An item can only be included in one Invoice Line.
C. One Invoice Line must include a minimum and a maximum of one Item.
D. The relationship between Item and Invoice Line is many-to-many.

C. Since the line representing the relationship has 2 single | marks on the item side of the diagram, the item has both a minimum and maximum of one in the relationship.

What is the E-R Diagram about an online pet food vendor showing?

An Invoice Line can include many Items.

An item can only be included in one Invoice Line.

One Invoice Line must include a minimum of one and a maximum of one Item.

The relationship between Item and Invoice Line is many-to-many.

One Invoice Line must include a minimum of one and a maximum of one Item.

The table relationship is One to Many Binary. It reads 1 item can be on many invoice lines. Many invoice lines can reference one item.

❌ An item can be included in many invoice lines.

❌ Invoice lines can reference one item

❌ The relationship is One to Many Binary

Which type of relationship exists between Student and Course in the diagram?

A. One-to-many binary
B. Many-to-many binary
C. One-to-many unary
D. Many-to-many unary

B. Since the outer icon on both sides of the line representing the relationship is a “crow’s foot” and the relationship includes two entities, the relationship would be binary many-to-many.

Unary is only for tables pointing at itself.

Which classification is correct for the box marked “Registration” in the given E-R Diagram?

A. Intersection data
B. Modality data
C. Cardinality data
D. Student data

A. The term intersection data refers to data which can be used to connect tables which do not share common fields by containing fields that are common to both other tables.

Which classification is correct for the box marked “Registration” in the given E-R Diagram?

Intersection data

Modality data

Cardinality data

Student data

Intersection data

Intersection data is a table created between two entities to store their relationship.

❌ Student data would be the Student table.

Which type of relationship exists between Location and Item in the diagram?

A. One-to-many binary
B. Many-to-many binary
C. One-to-many unary
D. Many-to-many unary

One-to-many binary. There are two tables.

Unary is only when there is one table and the table points to itself.