Bookmark and Share

Front Back
What is a database
A collection of data items.  Almost all organizations collect data - an orderly collection of data is called a database.
In a relational model, data is stored in
The word relational refers to ____ while a Relation is a _____.
set based mathematics from which tables are derived; table
Who created the Relational Model
E.F. Codd
Physical data independance means?
Data is isolated from the underlying hardware and software by high level abstractions.  This means the hardwae/software can change but the data always looks the same.
Where does the relational database get its name from?
A type of set in discrete mathematics.  A relation is a set in which the elements in one row are all related to each other. 
If a table contains two attributes that are each unique on their own, and either one could serve as a primary key, tese attributes are called
candidate keys
Every table must have what?
Primary key
How is a primary key used?
It is how the table is identified.  It is used for searching and linking tables together. 
What is a foriegn key?
the primary key column of one table that is placed into another table to link the two tables together
What is a composite key?
made up of two or more columns.  Sometimes there is no single column appropriate fro use as a primary key. 
What is a surrogate key?
usually a system-generated key composed of sequentially assigned integers.
True or False: Rows in a relational table need not be a unique.  there can be duplicate rows in a table.
A relationship between two tables is established by using a ____.
forieng key
An attribute's domain is
The set of legal values that an attribute may draw from
Give a short explaination of the mechanism whereby a row of one table can be linked to rows of another table
Primary key attribute of the parent table can be placed into the child table as a foreign key.
In a 1:Many relationship, the entity on the "one" side is the:
An ID-Dependant Entity is:
an entity who identifier (key) contains the identifier of another entity.
Give an example of an id-dependant entitity
Think of a room that belongs to a building.  The room number is based on bldnum/roomnum.  If you don't know both numbers, then you can't know the room.  This makes the room id-dependant because it includes the identifier of it parent intity.
Give an example of cardinality ratios
one to one, one to many, many to many
A table in a database is equivalent to a _______ in an ER Diagram, and a row is equivalent to a ________.
entitity class, entity instance
What is another name for row in a relational database structure?
Explain why all id dependant entities are week but not all weak entitites are id-dependant.
Think of the room/building example where a room is identified by it's bldnum/roomnum.  The room number by iteself cannot tell you anything about the room - it must have the building number to be properly identified - this makes it weak. For a non id-dependant example that is weak, look at an automobile which is identified by its VIN but is also identified by it's model (say a skylark).  Since a car IS a skylark but can be individually identified by its VIN.  Weak but not id-dependant. 
What is mandatory participation?
The minimum cardinatliy of a relationship is one or more.
True or False: The relationship name in an ER diagram can be different depending on the direction you read the relationship.
When building an ER diagram its important to determine all identifiers:
Give an example of when a mandatory participation constraint does not make the entity weak.
Students and Advisors.  A student must have an advisor, but just because the advisor leaves does not mean that the student is no longer identifiable.
What does weak entity mean?  Give an example and tell why it is weak.  Why would a database designer need to know it is weak?
A weak entitiy is one that cannot stand on it's own.  It is identified by it's relationship to the parent.  Report card would be a weak entitiy becasue in order for it to exist, a student would have to exist for it.  If the student no longer existed, the report card would have to be deleted because it makes no sense to have a report card without a student.  A database designer would have to know about this relationship so that when the objects are created he/she would have to know about the paring with the parent and if the parent is deleted he/she would have to know to delete it.
in SQL, how do you select a column named firstname from a table named employee?
select firstname from employee
IN SQL how do you select all the rows from a table named Employee where the value of the column FirstName is John?
select * from Employee where FirstName = 'John'
What SQL Statement is used to return only different values from a table, eliminating duplicates?
Select distinct
With SQL how can you return all the rows from a table named Employee sorted in descending order by FirstName
select * from Employee order by firstname desc
In SQL how do you list all of the rows of the Employee table and Dependant table that match on the employeeid attribute?
select * from Employee join dependant on employee.employeeid = dependant.employeeid
What query would we use to select all employees from the Emplyee table whos first name is the same as his last name?
select * from employee where firstname = lastname
What is the difference between an inner join and a left join? Example: select * from employee inner join depenantant on employeeid = dependantid select from employee left join dependant on employeeid = dependantid
The inner join returns only the rows in which the join attributes match and the left join returns all the rows on the left table along with the rows on the right table in which the join attributes match.
In SQL how do you add a new row to the table named employee?
Insert into Employee(columns) values (values to add)
IN SQL how do you change the value of an attribute in an existing row?
Update Employee set deptnum = 200 where empnum = 493
In SQL how do you delete a row from a table?
delete from employee where empnum = 449
In DDL Datadefinition Language you can do these major types of tasks:
1. Make new entitites
2. Change existing entitites
3. Eliminate existing entitites
What are the keywords for three major types of SQL statement for doing these three tasks
Create, Alter, Drop
The primary purpose of the subtype/supertype relationship is to
Eliminate type-inappropriate nulls
To implement a many to many relationship in a relational database you do this:
Add an intersection table between the two tables.  The intersection table will contain a foriegn key to each of the two tables being linked.
True or False: The relational model does not directly support many to many relationships.  Explain why or why not.
True.  relationships in the relational model are made by using foreign keys, however these keys can only make one to one or one to many relationships.  In order to support many to many relationships an intersection table must be created.
The hallmark of an id-dpendant entity is that it
has the identifier of another entitiy as part of it's own identifier.
An association data pattern is similar to a _______ relationship, but with the addition of extra attributes in the _________ table which transforms it to an association table.
many to many, intersection
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 carburator.  The carburator is composed of the body, the butterfly valve and the float chamber.  And it goes on.  Drow the pieces and see that it makes a tree, with the lawn mower at the top of the tree, the engine on the next level down, the carburetor, etc on the level below that and so on.  You mucst model this so it can be implemented in a database.  What data pattern do you use?
One to many recursive pattern.  By knowing the top level assembly you can locate all of the other parts of the assembly.  Sometimes it is used because a single lower level assumbly may be used by several upper-level assemblys. 
What type of patterns can a recursive pattern follow?
1:1, 1:M, M:M
If we want to record multiple e-mail address for each person in our Contact table, which data pattern would w use?
Multivalued attribute pattern
You see that a table named Abc contains a foreign key attribute named Z.  Would you expect Z to be a key of Abc? why or why not?
False.  Although it CAN be in the table as a foreign key, you would expect that it would be a key to another table.
Why is the use of surrogate keys usually considered a good practice?
surrogate key does not represent a fact about an entitiy.  it is a pure linking construct.  If a natural key were used in a table, the fact it represents may change.  This would vioate the unchangability of primary keys and weaken referential integrity.Additionally a natural key may turn out to be non-unique..surrogate keys would avoid this.
The where clause filters ______ while the having clause filters ______.
individual rows, groups
x of y cards Next >|