INSY 3280 Chapter 2 The Relational Model
List the Characteristics a table must have to be considered a relation.
-rows= records -columns=attributes of the entity -cells=hold values -each column has a unique name -order of columns/ row unimportant
Define the term unique key and give an example.
A unique key is a column whose values identify one and only one row. Ex: EmpID, ISBN
Describe a modification problem that occurs when changing data in the relation in question 2.32 and a second modification problem that occurs when deleting data in this relation.
Changes to owner data may need to be made in several rows. Deleting data for the last pet of an owner deletes owner data as well.
(DBMS)
Database Management System
Explain the meaning if the following expression: A-->(B,C)
It is true that A-->B A-->C The functional dependency: A--> (B,C) means that a value of A determines the value of both B and C.
Explain how primary keys and foreign keys are denoted in this book.
Primary keys are underlined and foreign keys are in italics.
Give an example of a relation.
School (ID, Name, DateFounded, No.of Students)
In the following equation , name the functional dependency and identify the determinants: Area= Length x Width
The functional dependency is: (Length, Width) --> Area The determinant is (Length, Width). BUT THIS IS DIFFERENT THAN SAYING THAT THAT LENGTH AND WIDTH ARE THE DETERMINANTS
identity seed
The starting value of a surrogate key
How do surrogate keys obtain their values?
They are supplied automatically by the DBMS.
The Schema is
a namespace (it is used as a prefix to the object name)
The unique values of the surrogate key
are assigned by DBMS each time a row is created, and the values never change
example of an entity
is a school.
what is a primary key
is the candidate key that is chosen as the key that the DBMS will use to uniquely identify eac row in a relation.
A database schema
is the design on which a database and its associated applications are built
A database management system (DBMS)
serves as an intermediary between database applications and the database
Relational DBMS
store data in the form of tables
A relational database
stores information in tables.
In a relational database,
tables are joined together using the value of the data.
Give an example of a relation with a unique composite key.
the combination (LastName, Department) where each column has no duplicate.
When creating a database it creates two files,
the log files and the data files.
Describe 4 uses of a primary key.
1. To identify a row 2. To represent the row in foreign keys 3. To organize storage for the relation 4.As a basis for indexes and other structures to facilitate searching in storage
SQL SERVER 2014 Express: Maximum relational Database size
10GB
SQL SERVER 2014 Express: Maximum memory utilized (per instance of SQL Server Database Engine)
1GB
Explain the term foreign key and give an example.
A foreign key creates the relationship between the tables; its key value corresponds to a primary key in a relation other than the one where the key is a primary key.
Define the terms functional dependency and determinant.
A functional dependency is a logical relationship in which the value of one item in the relationship can be determined by knowing the value of the other item. In: ISBN-> Title (Meaning if the ISBN is known, then we will also know the title) The determinant is the one to the left, whose value is known
What are composite keys
A key that contains two or more attributes
Define the tern nonunique key and give an example
A nonunique key not only identifies a row, but it potentially identifies more than one row. Ex: publisher
Define the term primary key in terms of functional dependencies.
A primary key is one or more attributes that functionally determines all of the other attributes.
What is a surrogate key, and under what circumstances would you use one?
A surrogate key is a unique, numeric identifier that is appended to a relation to serve as the primary key.
Give an example of a null value and explain each of the three possible interpretations from that value.
An example of null value would be: Null value for the attribute DeceasedDate in the table SUBSCRIBER. 1. Inappropriate: The Subscriber is a company 2.Known to be blank: Subscriber is alive 3.Unknown: Subscriber may be dead but date is unknown
If you assume that a relation has no duplicate data, how do you know there is always at least one primary key?
Because the collection of all the attributes in the relation can identify a unique row.
Explain the difference between primary key and a candidate key.
Both are unique identifiers. One is chosen to be the identifier for the relation and for foreign keys based on the relation. The other could be chosen as well, but since it is not, it is called a candidate.
Under what circumstances can an attribute of a relation be of variable length?
It can be of a variable length, if that attribute is considered to be a single thing like a memo or other variable length data item.
How does your answer to " If you assume that a relation has no duplicate data, how do you know there is always at least one primary key?" change if you allow a relation to have duplicate data?
It doesn't work—such tables do not have a primary key.
Explain the meaning of the following expression: (D,E)-->F
It is FALSE that D--> F and E-->F The PAIR (D,E) determines the value of F. It is a composite key.
SQL SERVER 2014 Express: Maximum Compute Capacity Used by a Single Instance (Analysis Services, Reporting Services)
Limited to lesser of 1 Socket or 4 cores
SQL SERVER 2014 Express: Maximum Compute Capacity Used by a Single Instance (SQL Server Database Engine)
Limited to lesser of 1 Socket or 4 cores
Define the term referential integrity constraint and give an example of one.
Referential integrity constraint is a rule specifying that every value of a foreign key matches a value of the primary key.
Why are the values of surrogate keys normally hidden from users on forms, queries, and reports?
Surrogate keys are normally hidden because they usually have no meaning to the users.
Give an example of a table not in relation.
Textbook (ISBN, Title, Authors) * A table is not a relation when there are multiple author names in the Authors column
identity increment
The amount that is added to each key value to create the next key value
Examine the data in the Veterinary Office List—Version One in Figure 1-30 (see page 52), and state assumptions about functional dependencies in that table. What is the danger of making such conclusions on the basis of sample data? what are the determinants of this relation? What attribute(s) can be the primary key of this relation? PetName --> (PetType, PetBreed, PetDOB, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail). OwnerEmail --> (OwnerLastName, OwnerFirstName, OwnerPhone) OwnerPhone --> (OwnerLastName, OwnerFirstName, OwnerEmail)
The danger is that there may be possibilities not apparent from sample data. For example, two owners might have pets with the same name. Determinants:PetName, OwnerEmail, OwnerPhone Candidate Keys: PetName
Examine the data in the Veterinary Office List—Version Two in Figure 1-31 (see page 52), and state assumptions about functional dependencies in that table. PetName -> (PetType, PetBreed, PetDOB, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) OwnerEmail -> (OwnerLastName, OwnerFirstName, OwnerPhone) OwnerPhone -> (OwnerLastName, OwnerFirstName, OwnerEmail) (PetName, Date) -> (Service, Charge)
The last functional dependency assumes a pet is seen at most on one day and that there is no standard charge for a service. Determinants: PetName, OwnerEmail, OwnerPhone, (PetName, Date) Candidate keys: (PetName, Date)
In your own words, describe the nature and purpose of the normalization process.
The purpose of the normalization process is to prevent update problems in the tables (relations) in the database. The nature of the normalization process is that we break up relations as necessary to ensure that every determinant is a candidate key.
Explain three possible interpretations of a null value
Three possible interpretations are: 1. Value not appropriate 2. Value known to be blank 3. Value appropriate and unknown
The four components of a database system are:
Users Database Application Database Management System (DBMS) Database
Relational databases are designed
to address many of the information complexity issues
MySQL uses the AUTO_INCREMENT function
to automatically assign surrogate key numbers.
Oracle Database uses a sequence function
to define automatically increasing sequences of numbers that can be used as surrogate key numbers.
Many database applications use SQL
to retrieve, format, report, insert, delete, and/or modify data for users
A database
is a collection of information that is organized so that it can easily be accessed, managed, and updated. In one view, databases can be classified according to types of content: bibliographic, full-text, numeric, and images.
A surrogate key
is a column with a unique, DBMS-assigned identifier that has been added to a table to be the primary key.
A null value
is a missing value in a cell in a relation
A database
is a self-describing collection of related records
A database application
is a set of one or more computer programs that serves as an intermediary between the user and the DBMS
A relation
is a two-dimensional table consisting of rows and columns
Structured Query Language (SQL)
is an international standard for creating, processing and querying databases and their tables.
Metadata
is data describing the structure of the database data.
Entity
is defined as something of importance to the user that needs to be represented in the database
Data file (.mdf)
is found in the filegroup
Transaction log (.ldf)
is found in the log file
Database
is made up of data files and transaction log files
Surrogate key
is short, numeric, and changes
An ideal primary key
is short, numeric, and never changes
What is alternate key
is similar to a primary key. It accepts null values; where as the primary key does not. The null values can be submitted to the attribute in a tuple.
The purpose of a database
is to keep track of things
Collation refers to
a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width
objects
are inside a Schema
Schema
are inside a database
What are candidate keys
are keys that uniquely identify each row in a relation
you can eliminate null values
by requiring an attribute value
relational database
contains a collection of separate tables, in which each table holds data about one and only one theme in most circumstances
