Exam 1

Question 1

4 components of database system

Answer 1

Users, DB app, DBMS, DB

Question 2

Database definition

Answer 2

self-describing collection of related records/integrated tables

Question 3

Integrated Tables

Answer 3

Tables that store both data and the relationships among data. Thus, have metadata.

Question 4

Tables that store both data and the relationships among data

Answer 4

Integrated Tables

Question 5

What is a DBMS?

Answer 5

A program used to create, process, and administer a DB.

Question 6

Difference between personal and enterprise DB systems?

Answer 6

In personal DB sys, applications and DBMS are combined from user’s perspective, while they’re separate in enterprise. Also, more powerful apps/features, usually. Personal also designed for single user/computer.

Question 7

Personal DB Sys example?

Answer 7

Microsoft Access

Question 8

Enterprise DB Sys examples?

Answer 8

Microsoft SQL Server, IBM DB2

Question 9

Referential Integrity Constraint

Answer 9

Every value of a foreign key must match a value of a existing primary key in another relation

Question 10

Every value of a foreign key must match a value of a existing primary key in another relation

Answer 10

Referential Integrity Constraint

Question 11

Metadata

Answer 11

Data describing the structure of the DB’s data

Question 12

Data describing the structure of the DB’s data

Answer 12

Metadata

Question 13

Function of DB applications?

Answer 13

Create and process forms and reports, process user queries, etc.

Question 14

Function of a DBMS?

Answer 14

Process SQL requests, create DBes/tables/relationships/keys, modify data, enforce rules and constraints, concurrency control and security, and perform backups and recovery.

Question 15

Types of anomalies

Answer 15

Deletion, update, insertion

Question 16

Alt names for table

Answer 16

Relation, file

Question 17

Alt names for row

Answer 17

Record, tuple

Question 18

Alt name for column

Answer 18

Attribute, field

Question 19

Primary key def

Answer 19

The chosen candidate key to be main key for a relation

Question 20

Composite key def

Answer 20

Key with 2+ attributes

Question 21

Surrogate key def

Answer 21

Added unique, numeric key to serve as primary key

Question 22

Foreign key def

Answer 22

Primary key from one table, placed into another table

Question 23

Functional dependency def

Answer 23

Where attribute(s) determine the value of other attribute(s) in the same table

Question 24

Determinant def

Answer 24

Attribute(s) that determine the value of other attribute(s) in the same table

Question 25

Functional dependency def

Answer 25

Relationship where attribute(s) determine the value of other attribute(s) in the same table

Question 26

Determinant def

Answer 26

Attribute(s) that determine the value of other attribute(s) in the same table

Question 27

Attribute(s) that determine the value of other attribute(s) in the same table

Answer 27

Determinant

Question 28

1NF

Answer 28

All tables are relation--no repeats, unique names, independent order, no multientries, same datatype cols, rows data, cols attributes

Question 29

2NF

Answer 29

No non-key attributes can be determined by a subset of the key/partial dependencies. If non-composite primary key, automatically 2NF! Cand key not non-key.

Question 30

No non-key attributes can be determined by a subset of the key. Form?

Answer 30

2NF

Question 31

Partial dependency

Answer 31

When a non-key attribute is dependent on only part of a candidate key

Question 32

When a non-key attribute is dependent on only part of a candidate key

Answer 32

Partial dependency

Question 33

3NF

Answer 33

No non-key attributes can be determined by a non-key attribute/transitive dependencies.

Question 34

No non-key attributes can be determined by a non-key attribute. Form?

Answer 34

3NF

Question 35

Transitive dependency

Answer 35

When a non-key attribute is determined by another non-key attribute. Compound key attributes not counted as non-key.

Question 36

When a non-key attribute is determined by another non-key attribute

Answer 36

Transitive dependency

Question 37

BCNF

Answer 37

No attributes are determined by a non-key attribute. If no compound candidate keys, automatic from 3NF!

Question 38

No attributes are determined by a non-key attribute. Form?

Answer 38

BCNF

Question 39

Normalization steps

Answer 39

ID a functional dependency. If determinant isn't a candidate key, copy functional dependency to new table, keeping the determinant in the original table as a foreign key, and deleting the rest. Repeat!

Question 40

Elements of the E-R model

Answer 40

Entities, relationships, attributes, identifiers

Question 41

Weak entity

Answer 41

Entity that cannot logically exist without another entity

Question 42

ID-dependent entity

Answer 42

Entity that cannot exist or be identified without another entity; solid line time!

Question 43

Identifying relationship

Answer 43

Relationship between strong and ID-dependent weak entity

Question 44

Relationship between strong and ID-dependent weak entity

Answer 44

Identifying relationship

Question 45

Super/subtypes

Answer 45

The subtype is a special case of the supertype; subtype determined by the discriminator attribute. Subtypes can be inclusive (can have multiple) or exclusive. Subtype entities ID-dependent on supertype parent.

Question 46

Attribute that determines subtype(s)?

Answer 46

Discriminator

Question 47

Entity when there are attributes of a relationship between 2 or more entities

Answer 47

Associative entity

Question 48

Steps from model to design?

Answer 48

1, entities to tables and identifiers to primary keys; 2, verify normalization; 3, specify attribute information; 4, relationships to foreign keys

Question 49

Attribute implementation details?

Answer 49

Data type, null status, default values, data constraints

Question 50

char vs varchar

Answer 50

char is fixed length, varchar isn't

Question 51

Intersection table

Answer 51

Table created for N:M relationships, where primary key of each connected table is used as compound key. If there's additional attributes, it's an association table!

Question 52

Table created for N:M relationships, where primary key of each connected table is used as compound key. No additional attributes.

Answer 52

Intersection table

Question 53

Intersection table, but with MORE PIZZAZZ

Answer 53

Association table

Question 54

Foreign keys for 1:1?

Answer 54

Slap PK of one into the other, doesn't matter which. Don't do both, though!

Question 55

Parent-child relationship

Answer 55

1:N relationship

Question 56

Foreign keys for 1:N?

Answer 56

Slap PK of parent into child as FK (not as primary key, though).

Question 57

Foreign keys for N:M?

Answer 57

Make an intersection table!

Question 58

Foreign keys for super/subtypes?

Answer 58

PK of supertype becomes foreign PK of subtypes.

Question 59

Foreign keys for recursive relationships?

Answer 59

For 1:1, 1:N recursive, put PK as a separate FK attribute EMPLOYEE(EmployeeNum, FirstName..., Supervisor), for N:M recursive, make intersection table with compound foreign PKs USER_INTERSECTION(Follower, Following)