mis 4113 Final exam quiz questions

RepName is a candidate key

Given the SQL statement: CREATE TABLE SALESREP ( SalesRepNointNOT NULL, RepNamechar(35)NOT NULL, HireDatedateNOT NULL, CONSTRAINTSalesRepPKPRIMARY KEY (SalesRepNo), CONSTRAINTSalesRepAK1UNIQUE (RepName) );We know that ________.

uses of SQL views

Hide columns or rows. Display results of computations. Hide complicated SQL syntax. Layer built-in functions. Provide level of isolation between table data and users' view of data. Assign different processing permissions to different views of the same table. Assign different triggers to different views of the same table.

True

If the table ITEM has a column WEIGHT, and WEIGHT has the data type Numeric (4,2), the value 4321 will be displayed as 43.21.

True

In 1:N relationships, the table on the "many" side is called the child.

True

In E-R modeling, an attribute describes the characteristics of an entity.

represent relationships where the child identifier does not include the key of the parent

In relational database design, ID-dependent entities are not used to ________.

How to create relationships

by placing the primary key of one entity in the other entity as a foreign key.

Attributes

• describe an entity's characteristics. •All entity instances of a given entity class have the same attributes, but vary in the values of those attributes.

Cascading Delete

•occurs when associated child rows are deleted along with the deletion of a parent row. -For strong entities, generally do not cascade deletes -For weak entities, generally do cascade deletes

Data definition language (DDL)

•statements -Used for creating tables, relationships, and other structures -Covered in this chapter (Chapter 7)

Data manipulation language (DML)

•statements -Used for: •Queries - SQL SELECT statement •Inserting data - SQL INSERT statement •Modifying data - SQL UPDATE statement •Deleting data - SQL DELETE statement

False

A relationship's maximum cardinality indicates the maximum number of entities that can participate in the relationship.

a trigger

A stored program that is attached to a table or view is called ________.

True

All weak entities must have a minimum cardinality of 1 on the entity on which it depends.

True

An SQL virtual table is called a view.

True

An attribute that determines which subtype is appropriate is called a discriminator.

True

Because SQL statements are table-oriented, whereas programs are variable-oriented, the results of SQL statements used in programs are accessed using an SQL cursor.

faster query response times

Because SQL stored procedures allow and encourage code sharing among developers, stored procedures give database application developers all these advantages except ________.

Both Define referential integrity constraint from child to parent and Make foreign key NOT NULL are correct

For the M-O (parent mandatory, child optional) case, what action(s) should be taken to ensure minimum cardinality is maintained?

SQL keyword CHECK

is used to limit the value range that can be placed in a column.

True

The SET keyword is used to specify a new value when changing a column value.

False

The SQL command CREATE USER VIEW is used to create a virtual table.

CREATE VIEW

The SQL command used to create a virtual table is ________.

attributes

The characteristics of a thing are described by its ________.

Difference between Char and VarChar

-CHAR is a fixed length string data type, so any remaining space in the field is padded with blanks. CHAR takes up 1 byte per character. So, a CHAR(100) field (or variable) takes up 100 bytes on disk, regardless of the string it holds. -VARCHAR is a variable length string data type, so it holds only the characters you assign to it. VARCHAR takes up 1 byte per character, + 2 bytes to hold length information. For example, if you set a VARCHAR(100) data type = 'Jen', then it would take up 3 bytes (for J, E, and N) plus 2 bytes, or 5 bytes in all. -You can see how the use of VARCHAR in most cases is preferred, to save space. -In most cases, you use Char for state abbreviation (CHAR(2)), or Fixed length product codes.

non-ID-dependent weak entities

-The identifier of the parent does not appear in the identifier of the weak child entity.

relationship between two 1:1 strong entities

-We can choose either table as the parent, and the other table as the child. -Either design will work -Minimum cardinality considerations may be important •O-M will require a different design than M-O •One design will be very preferable

entity instance

-the occurrence of a particular entity class

CREATE VIEW Statement

/* *** SQL-Query-View-CH07-01 *** */ SELECT * FROM CustomerNameView ORDER BY CustomerLastName, CustomerFirstName; /* *** EXAMPLE CODE - DO NOT RUN *** */ /* *** SQL-ALTER-VIEW-CH07-01 *** */ ALTER VIEW CustomerNameView AS SELECT FirstName AS CustomerFirstName, LastName AS CustomerLastName, FROM CUSTOMER;

the steps for transforming a data model into a database design

1. Create a table for each entity: - Specify the primary key (consider surrogate keys, as appropriate) - Specify alternate (candidate) keys - Specify properties for each column: * Null status * Data type * Default value (if any) * Data constraints (if any) - Verify normalization 2. Create relationships by placing foreign keys - Relationships between strong entities (1:1, 1:N, N:M) - Identifying relationships with ID-dependent entities (intersection tables, association patterns, multivalued attributes, archetype/instance patterns) - Relationships between a strong entity and a weak but non-ID-dependent entity (1:1, 1:N, N:M) - Mixed relationships - Relationships between supertype/subtype entities - Recursive relationships (1:1, 1:N, N:M)

False

A binary relationship is a relationship based on numerical entity instance identifiers.

False

A composite identifier is defined as a composite attribute that is an identifier.

True

A data constraint is a limitation on data values.

True

A data model is a plan for a database design.

False

A default value is the value the user enters into the row the first time the user enters data.

False

A null value is an attribute value that has been set to zero.

cascading delete

A referential integrity constraint policy that insures that all rows containing a foreign key value in a table are eliminated from the table when the row containing the corresponding primary key value in a parent table is eliminated from the database is called _____________

cascading updates

A referential integrity constraint policy that insures that foreign key values in a table are correctly maintained when there is a change to the primary key value in the parent table is called ________.

weak entity

An entity whose identifier includes the identifier of another entity is a(n) ________.

False

An exclusive subtype pattern has one supertype entity that relates to one or more subtype entities.

entity instance

An occurrence of a particular entity is called an ________.

True

Association tables sometimes connect more than two entities

True

Data values to be added to a table are specified by using the VALUES clause.

False

Deciding whether or not an attribute is required is determined during the data modeling phase.

True

Entities can be associated with one another in relationships.

False

It is easy to enforce the referential integrity actions for N:M relationships.

the most instances of one entity class that can be involved in a relationship instance with another entity class

Maximum cardinality refers to ________.

Numeric (p[,s])

Numeric works identically to Decimal.

True

One advantage of using the CONSTRAINT command to define a primary key is that the database designer controls the name of the constraint.

True

One of the important properties of a column is whether or not it can have a NULL value.

M:M

Recursive relationships can have all of the following maximum cardinalities except ________.

uses of ID-dependent entities

Representing N:M Relationships Representing association relationships Storing multivalued attributes Representing archetype/instance relationship

False

SQL triggers are created using the ADD TRIGGER statement.

True

Subtypes can be exclusive or inclusive.

True

The CHECK keyword is used to limit column values to specific values.

False

The MODIFY keyword is used to change a column value.

True

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

create a table for each entity

The first step in transforming an extended E-R model into a relational database design is to ________.

True

The last step in creating a table is to verify table normalization.

True

The notation 1:N shows a relationship's maximum cardinalities.

True

The values of a surrogate key have no meaning to the users.

True

There are three types of recursive relationships: 1:1, 1:N, and N:M.

the key of the parent is placed as a foreign key into the child

To represent a one-to-many relationship in a relational database design, ________.

True

To represent an N:M relationship in a relational database design, an intersection table is created.

•data model is a plan or blueprint for a database design. •A data model is more generalized and abstract than a database design. •It is easier to change a data model then it is to change a database design, so it is the appropriate place to work through conceptual database problems.

Understand what a data model is. Understand the difference between a data model and a database design. Understand the relationship between a data model and a conceptual design.

True

Unless it is being used to copy data from one table to another, the INSERT statement can be used to insert only a single row into a table.

True

When creating a table for an ID-dependent entity, both the key of the parent and the key of the entity itself must appear in the table.

False

When the correct SQL command is used to delete a table's structure, the command can only be used with a table that has already had its data removed.

The data in the table is also deleted.

When the correct SQL command is used to delete a table's structure, what happens to the data in the table?

Truee

When the parent entity is required (M) in a relationship, every row of the child table must have a valid, non-null value of the foreign key.

INSERT

Which SQL keyword is used to add one or more rows of data to a table?

ALTER

Which SQL keyword is used to change the structure, properties or constraints of a table?

SET

Which SQL keyword is used to change the values of an entire column?

-an intersection table is not needed -The foreign keys in the intersection table can't refer to the same attribute in the parent table are correct.

Which of the following is not true about N:M recursive relationships?

subtype entity

___________ entity is a special case of a supertype entity: -STUDENT : UNDERGRADATE or GRADUATE

Identifiers

_________________ are attributes that name, or identify, entity instances. •The ____________ of an entity instance consists of one or more of the entity's attributes.

Minimum Cardinality

• is the minimum number of relationship instances in which an entity must participate. •are generally stated as either zero or one: -IF zero [0] THEN participation in the relationship by the entity is optional, and no entity instance must participate in the relationship. -IF one [1] THEN participation in the relationship by the entity is mandatory, and at least one entity instance must participate in the relationship. ***Know Symbols of max/min slide 34 ch 5

Inclusive subtypes

• one supertype can relate to one or more subtypes.

Exclusive subtypes

• one supertype relates to at most one subtype, indicating by the X in the circle.

ID-Dependent

•(a weak entity) is an entity (child) whose identifier includes the identifier of another entity (parent). •is a logical extension or subunit of the parent: -BUILDING : APARTMENT -PAINTING : PRINT •The minimum cardinality from the ID-dependent entity to the parent is always one. •The minimum cardinality to the ID-dependent entity depends. •All ________________ entities are considered weak.

Views do NOT support the ORDER BY clause, thus you cannot sort within a view.

•Create the CustomeNameView on the CUSTOMER table that displays the customer's LastName and FirstName and rename the fields. /* *** SQL-CREATE-VIEW-CH07-01 *** */ CREATE VIEW CustomerNameView AS SELECT LastName AS CustomerLastName, FirstName AS CustomerFirstName FROM CUSTOMER;

relationship between two N:M strong entities

•In an ______ strong entity relationship there is no place for the foreign key in either table. -A COMPANY may supply many PARTs -A PART may be supplied by many COMPANYs •The solution is to create an intersection table that stores data about the corresponding rows from each entity. •The intersection table consists only of the primary keys of each table which form a composite primary key. •Each table's primary key becomes a foreign key linking back to that table. COMPANY_PART_INT (CompanyName, PartNumber)

relationship between two 1:N strong entities

•Place the primary key of the table on the one side of the relationship into the table on the many side of the relationship as the foreign key. •The one side is the parent table and the many side is the child table, so "place the key of the parent in the child."

Relationship of identifiers and attributes

•The identifier of an entity instance consists of one or more of the entity's attributes.

identifier of an entity/the primary key of a table relationship

•The identifier of the entity usually becomes the primary key of the table. •However, the ideal primary key is short, numeric, and fixed.

Alternate keys

•The terms candidate key and _____________ are synonymous. •Candidate keys are alternate identifiers of unique rows in a table. •Will use AKn.m notation, where n is the number of the alternate key, and m is the column number in that alternate key.

Disadvantages of surragate keys

•They have no meaning to users (an ID used as a surrogate key may give no indication of the related table) •When data is shared with among different databases (an ID used in one database may have no meaning to a user in another database)

Composite identifiers

•are identifiers that consist of two or more attributes. •Identifiers in data models become keys in database designs. -Entities have identifiers. -Tables (or relations) have keys.

discriminator

•attribute which indicates the subtype.

Supertype entity

•contains all common attributes, while the subtypes contain specific attributes. •The ______________ may have a discriminator attribute which indicates the subtype.

Null Status

•indicates whether or not the value of the column can be NULL.

database design

•is a set of database specifications that can actually be implemented as a database in a specific DBMS product.

SQL View

•is a virtual table that is constructed from other tables or views. •It has no data of its own, but obtains data from tables or other views. •SELECT statements are used to define views: -A view definition may not include an ORDER BY clause •SQL views are a subset of the external views: -They can be used only for external views that involve one multivalued path through the schema

Strong entity

•is an entity that represents something that can exist on its own. -Examples (PERSON, AUTOMOBILE, BUILDING)

Weak entity

•is an entity whose existence depends on the presence of another entity. -Example (APARTMENT - depends on BUILDING)

ideal primary key

•is short, numeric, and fixed.

Maximum Cardinality

•is the maximum number of relationship instances in which an entity can participate. -One-to-One [1:1] -One-to-Many [1:N] -Many-to-Many [N:M]

Surrogate keys

•meet the ideal: •Unique within the table, and they never change. •Assigned when the row is created; destroyed when the row is deleted. •Designed to be short, numeric and fixed.

Cascading Update

•occurs when a change to the parent's primary key is applied to the child's foreign key. -Surrogate keys never change and there is no need for __________________ when using them