ISC301-Test2

The two steps of designing a database are:

1. Create a data model, in which we out the complexities of the database design 2. transforming the data model into a database design by adding database design features (such as foreign keys, intersection tables...)

Identify the three major tasks for transforming a data model into a database design

1. Replacing entities and attributes with tables and columns 2. Representing relationships and maximum cardinalities by use of foreign keys 3. Representing minimum cardinalities by defined actions to constrain the values of primary and foreign keys

Why is the choice of the primary key important? (3)

1. The DBMS will use it to facilitate searching and sorting of table rows 2. Some DBMSs use it to organize table storage 3. Most DBMS products create indexes and other data structures using the value of the primary key.

What are surrogate keys advantages? (3)

1. Unique within the table and never change 2. assigned when the row is created and destroyed when the row is deleted 3. It is an ideal primary key- short, numeric, fixed

describe three ways that a default value can be assigned.

1. a constant 2. the result of a function 3. the result of a calculation

Explain two ways forms and reports are used for data modeling

1. determine the structure of the data model 2. validate the data model

describe two tests for determining if an entity is strong/

1. does the entity have an identifier of its own? 2. does the entity seem logically different and separate from other entities?

list four uses for ID-dependent entities

1. for representing N:M relationships 2. for creating association relationships 3. for sorting multivalued attributes 4. for creating archtype/instance relationships

Describe two disadvantages of surrogate keys.

1. foreign keys that are based on surrogate keys have no meaning to the users. 2. when data is shared among different databases, it is possible for two different SALE_ORDER rows to have the same value of ID

ID-Dependent relationships are always :

1:N

What distiguishes a weak entity from a strong entity that has a required relationship to another entity?

A strong entity that has a required relationship with another entity can and will exist without the presence of the other, strong entity. A weak entity cannot and does not exist without the presence of the other, strong entity.

Give examples of 1:1, 1:N, N:M relationships

Agent to Car (1:1) Agent to Client (1:N) Property to Client (N:M)

Give examples of M-M, M-O, O-M, O-O relationships

Agent to Vehicle (M-M) Agent to Client (M-O) Property to Client (O-O)

The table QUOTATION has a composite key of CompanyName and PartNumber. It also has a non-key attribute of Price. This is an example of:

An association table

list five generic data types

CHAR(n), VARCHAR(n), DATE, TIME, MONEY, INTEGER, and DECIMAL.

Suppose two entities called STUDENT and COURSE have an N:M relationship. What should be done to transform the data model to a database design?

Create three table; place a FK for both Course and Student in the third table

suppose two entities called OFFICE and EPLOYEE have a 1:1 relationship/ When transforming the entities to tables, where should teh foreign key be place?

Either the OFFICe or EMPLOYEE table

Explain the difference between IS-A and HAS-A relationships

Entities with an IS-A relationship should have the same identifier because they represent different aspects of the same things. Entities with HAS-A relationships represent aspects of different things and thus have different identifiers. These relationships do not involve subtypes. (relationship between supertype and subtypes is sometimes called an IS-A)

explain how to differentiate and N:M strong entity pattern from an association pattern

In general, if there are one or more additional attributes associated with the relationship between two strong entities in an otherwise N:M strong entity pattern, then an association pattern is needed. In the data model, this will be shown as a third, weak entity that is ID-dependent on both of the other entities.

How can you assess minimum cardinality for entities in forms?

In general, you cannon determine minimum cardinality from forms and reports

Explain why two entities are needed to model multivalued attributes

In the E-R model, all attributes must have a single value. Therefore, multivalued attributes must be modeled with a second table to hold the multiple values of teh attribute.

what does the notation LastName (AK2.2) mean?

LastName is the second column in a composite alternate key

Explain how to determine the minimum cardinality of both sides of an ID-Dependent relationship.

The ID-dependent entity (the "child" ) channot exist without the entity upon which it is dependent (the "parent"). Therefore, the minimum cardinality from the ID-dependent entity to the parent is always one.

describe the archetype/instance pattern.

The archetype/instance pattern generally has one entity that is a manifestation (or "instance") of another (logical abstraction or "archetype") entity. The archtype is usually an abstract concept that is actually seen in the real world as the instances.

What is the degree of relationship?

The number of entity classes in the relationship is the degree of the relationship

Describe the relationship between the structure of forms and reports and teh data model.

They both determine one another

What is the relationship between entities and tables? between attributes and columns?

When transforming a data model into a database design we create a table for each entity in the database design and a column for each attribute.

an alternate key is the same as

a candidate key

What is a binary relationship?

a relationship btwn two entity classes

What is an identifying relationship? How is it used?

a solid line is used to represent an identifying relationship and dashed line to represent a nonidentifying relationship

Define subtype and supertype/

a supertype is an entity that contains a set of attributes common to what would otherwise be modeled as several entities. a subtype is an entity that contains the specialized, noncommon attributes from the several entities

what is an intersection table?

a talbe that stores the foreign key combinations that link two entities in an N:M strong entity relationship. contains ONLY the two columns holding the foreign keys

What is a surrogate key?

a unique, DBMS-supplied identifier used as the primary key of a relation

what is a discriminator

an attribute of the supertype entitiy that identifies the associated subtype entity

What is the difference between an entity and a table?

an entity is a database design concept while a table is the implementation of that entity in an actual database. Main difference is entities can be created without specifying foreign keys, tables must have foreign keys created to implement relationship

What is a weak entity?

an entity whose existence depends upon the existence of another entity. ALL ID-dependent entities are weak entities, BUT NOT all weak entities are ID-dependent.

Define identifier.

are attributes that name, specify, locate entity instances.

Name three patterns taht use ID-dependent relationships

association pattern, multivalued attribute pattern, archetype/instance pattern

What does cardinality mean?

count

The first step in transforming a data model to a database design is to;

create a table for each entity in the data model

Define attribute.

describes the entitity's characteristics

Suppose an attribute called Gender must have a value of M or F. This is an example of a:

domain constraint

Explain why tow forms or reports are usually needed to infer maximum cardinality.

each form or reports only shows he maximum cardinality in one direction between the entities. therefore, to know the cardinalities in both directions requires two forms or reports

why can primary keys never be null?

each row must have a unique identifier

What is the advantage of making changes during the data modeling stage?

easier, simpler, faster, and cheaper

Define composite identifier, give example.

identifiers that consist of two or more attributes are called composite identifiers. EX. AreaCode, LocalNumber OR City, State

Explain the difference between exclusive subtypes and inclusive subtypes.

in a group of exclusive subtypes, the supertype is associated with at most one subtype. in a group of inclusive subtypes, the super type can be associated with one or more of the subtypes.

Suppose two entities called OFFICE and EMPLOYEE have a 1:N relationship. When transforming the entities to tables, where should the foreign key be placed?

in teh EMPLOYEE table

What entity type should come to mind when you see the words "For use by" in a form?

indicate a supertype/subtype entity pattern

difference btwn talbe that represents and ID-dependent association entity and an intersection table?

intersection table contains only the two column containing the foreign key links to the two tables in the N:M relationship, but an ID-dependent association entity contains additional columns that hold attribute values related to each row in teh association entity/

Defie relationship

is an association between two or more entity classes

describe how to represent a mixed entity relationship

it is a combination of strong and ID-dependent designs. Respresent it by: 1. creating an ID depenedent relationship btwn the appropriate parent entity and teh ID-dependent child entity. this will form a typical id-dependent composite primary key in teh ID-dependent child entity. 2. add the primary key of the strong entity into the ID-dependent child entity as a foreign key, but not as part of the child entity's primary key

what is an interrelation constraint?

limits the values of a column by a comparison to the values of another column in a different table

what is an intrarelation constraint?

limits the values of a column by a comparison to the values of another column in the same table

what is a domain constraint

limits the values of a column to a specific set of values (green, blue, yellow)

what is a range constraint

lims the values of a column to a values within a specified range of values (1 to 10)

what tasks should be accomplished when verifying normaliation of a database design?

make sure al tables are in BCNF and 4NF

Define maximum cardinality and minimum cardinality

max cardinality is the largest number of entities that can occur on one side fo the relationship, min is the smallest

a primary key should never be

null

name four column properties

null status, data type, default value, data constraints

why can alternate keys be null?

one use of designating a column as an alternative key is simply to guarantee uniqueness of populated fields

how to represent a version(archtype)/instance entity relationship

place the instances in a separate table

1:1 strong entity relationship

put one of the primary keys as a foreign key in either of the two entitites

N:M strong entity relationship

put the primary key of each of the entities into a separate table forming two 1:N relationships. New table is called an intersection table-- it will have composite primary key consisting of the two foreign keys in each row

1:N strong entity relationship

put the primary key of the parent entity (one) in the child entity (many) as a foreign key

describe how to represent a multivalue attribute entity relationship

replace the multivalued attribute with a table and replace each multivalued attribute with a column in the new table

describe how to represent an association entity relationship

represented by storing primary key values of two entities in an N:M strong entity as a composite primary key and then adding one or more additional columns to the table to store attribute values taht are specific to the rows of the table

and ideal primary key is a

short number field such as EmployeeNumber

what are the three characteristics of an ideal primary key?

short, numeric, and fixed

Define entity.

something that the users want to track and is readily identifiable in their environment

What happens when an instance entity is given a non-ID-dependent identifier? How does this change affect relationship design?

the relationship changes from an ID-dependent relationship to a weak but non-ID-dependent relationship. the design no longer requires the primary key of the archtype entity to appear in the instance entity as part of the primary key

What happens when the parent in an ID-dependent relationship is given a surrogate key? What should the key of the child become?

there is no change in teh database design itself but any meaning associated with the orignial parent primary key is lost. should also give the child entity a surrogate key and change it into a weak but non-ID-dependent entity

difference between alternate key and a candidate key?

they are the same

What is the most important reason for using subtypes in a data model?

to avoid value-inappropriate null values. If all the attributes in the subtypes appeared in the supertype, there would be null values in such columns.

What rules exist when creating an instance of an ID-dependent entity? and when deleting the parent on one?

to create, the parent entity upon which it depends must have already been created. to delete, all associated instances of the dependent entity must be deleted as well.

describe how to represent a supertype/subtype entity relationship.

use the same entities in teh original database model with the primary key of the superkey as both the primary key and the foreign key in each of the subtypes/

When should you use a surrogate key?

when a talbe does not have a unique key, or when a unique key is too long, non-numeric and may change.