Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key

image

Play button

image

Play button

image

Progress

1/60

Click to flip

60 Cards in this Set

  • Front
  • Back

Do database tables have unique names?

Yes.

What does a table in a relational database represent? What do its rows represent?

1) Tables correspond to relations.




2) Each row represents a single entity or relationship (tuples).

Relations consist of ________ and ________.

1) A relation instance.




2) A relation schema.

What is a relation instance?

An actual with table with a particular set of rows.

What is a relation schema?

The column headings of the table.

What does a schema consist of?

1) The relation's name.


2) The name of each column in the table.


3) The domain of each field/attribute (column).




eg. Customer = {sin, firstName, lastName, age, income}

Draw the relation instance for:




Customer = {sin, firstName, lastName, age, income}

What is the domain of a relation instance?

A subset of the Cartestian product of the list of domains.




In practice a relation instance is usually some small subset of the Cartesian product.

What is a relationship instance?

A set of records (tuples) that has the same number of fields as the schema.

What is the degree of a relation?

The number of fields.

What is the cardinality of a relation instance?

The number of records it contains.

What does SQL stand for?

Structured Query Language.

What is the DML and what does it do?

Data manipulation language which allows users to create, modify and query data.

What is the DDL and what does it do?

Data definition language is used to define external and conceptual schemas.




The DDL supports the creation, deletion and modification of tables including the specification of domain constraints and other constraints.

What statement is used to create a table in SQL?



Is SQL case sensitive?

SQL keywords are not. Name objects may be.

What statement is used to insert a record into an existing table?



In regards to insertion of a record into a table, what can be said about the list of column names? Data for some columns?

The list of column names is optional but the values must be in the same order as the columns.




Data for some columns may be omitted in which case the column names must be specified.

What statement is used to delete a record?





The WHERE clause specifies the record(s) to be deleted.

Omitting this deleted all the records in a table.








The WHERE clause specifies the record(s) to be deleted.




Omitting this deleted all the records in a table.

What statement is used to modify a record(s) in a table?



What statement is used to delete a table? What is important about this?

DROP TABLE




This also deletes the table schema.

What statement is used to add or remove columns?

ALTER TABLE


ADD height INTEGER




ALTER TABLE


DROP height

What is an integrity constraint? When should they be specified?

Restrict the data that can be stored in a DB to prevent invalid data being added to the DB.




When the DB schema is defined.

What are four types of constraints?

1) Domain constraint.


2) Key constraint.


3) Foreign key constraint.


4) General constraint.

What is a key constraint?

States that a minimal subset of the fields in a relation is unique.

What statements are used to specify key constraints?

UNIQUE - identifies candidate keys.




PRIMARY KEY - identifies the primary key.

What are candidate keys useful for?

Preventing duplicate data from being entered into the DB.

Create a Patient table with sin as primary key, msp as candidate key, firstName, lastName, age.

What does a foreign key constraint do?

References the primary key of another relation.




The value of the foreign key attribute(s) must match a primary key in the referenced table.

Specify this.

Specify this.

CREATE TABLE Account (


accountNumber INTEGER,


type CHAR(5),


balance REAL,


customerSIN CHAR(11),


PRIMARY KEY(accountNumber),


CONSTRAINT fk_customer FOREIGN KEY


(customerSIN) REFERENCES Customer)

What are two types of general constraints?

1) Table constraints associated with a single table.


2) Assertions which may involve several tables and are checked when any of these tables are modified.

What are two ways a primary key constraint can be violated?

1) A record can be changed or inserted so that it duplicates the primary key of another record in the table.


2) A record can be changed or inserted so that (one of) the primary key attributes is null.




Both cases lead to a rejected transaction.

A deletion or update transaction in the referenced table may violate a foreign key. Name four responses possible in SQL.

1) NO ACTION - reject transaction (default).


2) CASCADE - delete or update referencing record.


3) SET NULL - set the referencing record's foreign key attribute(s) to null (only on deletion).


4) SET DEFAULT - set the referencing record's foreign key attribute(s) to a default value (only on deletion). The default value must be specified in the foreign key.

A table that represents an entity set should have which four characteristics?

1) One column for each attribute.


2) The domain of each attribute should be known and specified in the table.


3) The primary key should be specified in the table.


4) Other constraints that have been identified outside the ER model should also be created where possible.

Create the table in SQL.

Create the table in SQL.



What are the two attributes of a relationship set?

1) The primary keys of the participating entity sets.


2) Any descriptive attributes of the relationship set.

What do the mapping cardinalities of the entities involved in a relationship determine?

1) The primary key of the relationship.


2) Whether or not the relationship needs to be represented as a separate table.

How should foreign keys be created?

Created for the attributes derived from the participating entity sets in a relationship set.

A relationship set with no cardinality constraints requires what?

A separate table to represent a relationship with no cardinality constraints (ie. many-to-many).

What is the primary key of a relationship set with no cardinality constraints?

A compound key made up of the primary keys of the participating entity sets.

Create the table.

Create the table.



Create the table.

Create the table.

For a relationship set with cardinality constraints...




1) If the relationship is one-to-one, what can be said about its primary key?




2) If the relationship is many-to-one, what can be said about its primary key?

1) The primary key can be either of the primary keys, but only one.



2) The primary key is from the entity set that can only appear once.

1) If the relationship set has no cardinality constraints, the primary key is considered ______?




2) If the relationship set has at least one cardinality constraint ______?

1) A compound key.




2) The primary key is taken from the entity that can be involved in at most one relationship.

1) When does a binary relationship not require a table?




2) How do we represent this in a table?

1) At least one of its entities has a key constraint.




2) Add the relationship set's attributes to the table for the entity set that provided the primary key. The attributes from the other entity sets involved in the relationship are specified as foreign keys.

Example of many-to-one.

Example of many-to-one.

1) Since an employee can only work in one branch there is still only one row for each employee in the Employee table.



2) No table is created for the works_in relationship.




3) Attributes folded into Employee table from works_in and Branch primary key.

Where possible, participation constraints should be included in a table specification.




When should you declare attributes NOT NULL?




What are other ways to model PCs?

This approach only works when a relationship is not represented in a separate table.




Some participation constraints must be modeled using assertions or triggers.

Study the examples in the slides.

OK.

What are three characteristics of weak entity sets?

1) Total participation in the identifying relationship.

2) A cardinality constraint with the identifying relationship.


3) A partial key.

A weak entity set should ___________ and __________?

1) Include a foreign key (to its owner entity set), the attributes of which are part of the WES' primary key.


2) Specify the foreign key as ON DELETE CASCADE.

Weak entity set example.

What are two basic approaches to translating class hierarchies to tables?

1) Create separate tables for the superclass and for each subclass. Superclass only contain its own attributes. Subclass contain primary key attributes of superclass and its own attributes. Cascade deletion of superclass records to subclasses.


2) Create tables for the subclass only which contain their attributes and all of the attributes of the superclass. The primary key is the primary key of the superclass. This assumes that there are no entities in the superclass that are not entities in a subclass.

How is an aggregate entity represented in a table?

A table defining the relationship set in the aggregation.

The relationship set between the aggregate entity and the other entity has the following attributes _________?

1) The primary key of the participating entity set.


2) The primary key of the relationship set that defines the aggregate entity.


3) Its own descriptive attributes (if any).

In what case is no table required for an aggregate entity?

1) If there is total participation between the aggregate entity and its relationship AND


2) If the aggregate entity does not have any descriptive attributes.




--> Insert the attributes of the aggregate entity into the table representing the relationship with that entity.

What are views useful for?

1) Convenience.


2) Security.


3) Independence.

Consider a DB containing these two tables:




Branch =(branchName, managerSIN, budget) Employee = (sin, name, salary, birthDate, branchName) , where branchName in Employee is a foreign key




A view is needed to access the employee's SIN,name and manager’s SIN

Generally, how are updates to views allowed?

Derived from a single table.

What is a superkey?

Two distinct tuples in a relation instance, cannot have identical values in all the fields of a superkey (or if they do they must have all of the exact same values).

slide 68 (functional dependencies)

slide 68 (functional dependencies)