CIS117 Final Exam

Natural key

(natural identifier) A general accepted identifier for real-world objects. As its name implies, a natural key is familiar to end users and forms part of their day-to-day business. Usually, if an entity has a natural identifier, a data modeler uses it as the primary key of the entity being modeled.

Differential backup

-only things that have changed since the last full backup -if you do differential backup everyday you need last full backup and differential and you get the best

Transaction log backup

-this is a record of some of the changes that have been updated. In addition to differential backup, these are done so you can repeat transactions that have been made since the differential backup.

What can end users use PL/SQL to create?

1. Anonymous PL/SQL blocks 2. Triggers (covered in section 8-7a) 3. Stored procedures 4. PL/SQL functions

What level of normal form do most businesses require for transactional databases?

3NF

Ch.9 How many phases are in the SDLC? (Systems Development Life Cycle)

5 1. planning: making map 2. Analyze 3. Detailed systems design: designs systems figure out what hardware and software needed 4. Implementation: up and running 5.Maintenance: Every

What is an example of an attribute?

A CUSTOMER entity would be described by attributes such as customer last name, customer first name, customer phone number, customer address, and customer credit limit.

Persistent storage module (PSM)

A block of code with standard SQL statements and procedural extensions that is stored and executed at the DBMs server.

Field

A character or group of characters (alphabetic or numeric) that has a specific meaning. A field is used to define and store data.

Attribute

A characteristic of an entity or object. An attribute has a name and a datatype.

What is a data integrity problem?

A data integrity problem is incorrect data which causes problems. For example, it is possible to enter a nonexistent sales agent's name and phone number in the the CUSTOMER file, but customers are not likely to be impressed if the insurance agency supplies the name and phone number of an agent who does not exist.

Fan trap

A design trap that occurs when one entity is in two 1:M relationships with other entities, thus producing an association among the other entities that is not expressed in the model. To fix this, you can create another link to the other entity that needs a relationship identified.

Secondary key

A key used strictly for data retrieval purposes. For example, customers are not likely to know their customer number (primary key), but the combination of last name, first name, middle initial, and telephone number will probably match the appropriate table.

Schema

A logical grouping of database objects, such as tables, indexes, views, and queries, that are related to each other.

Record

A logically connected set of one or more fields that describes a person, place, or thing. For example, the fields that constitute a record for customer might consist of the customer's name, address, phone number, date of birth, credit limit, and unpaid balance.

natural identifier

A natural key or natural identifier is a real-world, generally accepted identifier used to distinguish that is uniquely identify real world objects. As its name implies, a natural key is familiar to end users and forms part of their day to day business vocabulary.

Entity

A person, place, thing, concept, or event for which data can be stored. In this picture the entities are STUDENT, INSTRUCTOR, and COURSE.

Triggers

A procedural SQL code that is automatically invoked by the relational database management system when a data manipulation event occurs.

What is an example of a relationship in a database system?

A relationship exists between customers and agents that can be described as follows: an agent can serve many customers , and each customer may be served by one agent. Data models use three types of relationships: one-to-many, many-to-many, and one-to-one.

Constraint

A restriction placed on data, usually expressed in the form of rules. For example, "A student's GPA must be between 0.00 and 4.00."

What is a subquery?

A subquery is a SQL query nested inside a larger query.

Subtype discriminator

A subtype discriminator is the attribute in the supertype entity that determines to which subtype the supertype occurrence is related.

Provide more information about synonyms

A synonym is the opposite of a homonym and indicates the use of different names to describe the same attribute. For example, car and auto refer to the same object. Synonyms must be avoided whenever possible.

Chapter 5 Surrogate key

A system assigned primary key, generally numeric and auto incremented. Think of the recent project you did. You allowed the system to give instructor a surrogate key.

What is the SQL syntax to modify a table?

ALTER TABLE; or MODIFY TABLE;

What is a full backup?

All database objects are backed up in their entirety. -restore everything

Procedural Language (PL/SQL)

An Oracle-specific programming language based on SQL with procedural extensions designed to run inside the Oracle database. The general definition however is, that this is a language that makes it posssible to use and store procedural code and SQL statements within the database and to merge SQL, and traditional programming constructs, such as variables, conditional processing (IF, THEN, ELSE) basic loops (FOR, WHILE loops) and error trapping.

Relationship

An association between entities.

Foreign key

An attribute or attributes in one table whose values must match the primary key in another table or whose values must be null.

Superkey

An attribute or attributes that uniquely identify each entity in a table. Super Key: The set of attributes which can uniquely identify a tuple is known as Super Key. For Example, STUD_NO, (STUD_NO, STUD_NAME) etc. Adding zero or more attributes to candidate key generates super key. A candidate key is a super key but vice versa is not true

Composite Attribute

An attribute that can be further sub-divided to yield additional attributes. For example, a phone number such as 615-898=2368 may be divided into an area code (615), an exchange number (898) and a four-digit code (2368). Compare to simple attribute.

Simple Attribute

An attribute that cannot be subdivided into meaningful components. For example, age, sex, and marital status would be classified as simple attributes.

What is a sequence?

An object for generating unique values for a field.

What are the differences between 1NF, 2NF, 3NF, BCNF, and 4NF?

As one normalizes data from 1 to 4, it gets more detailed and more specific. The higher a normalization goes, the more in depth and special things become. 1NF = table format, no repeating groups, and PK identified 2NF = 1NF and no partial dependencies 3NF = 2NK and no transitive dependencies 4NF = 3NF and no independent multi-valued dependencies

What is the SQL syntax to permanently save a transaction/data in the database.

COMMIT;

What is the SQL syntax to delete a table?

DROP TABLE;

What does data integrity entail?

Data integrity is referring to the accuracy and consistency of data.

Who is responsible for testing and fine-tuning database performance, integrity, access, and security?

Database administrator

What is an example of a disjoint constraint?

Disjoint constraints Disjoint Describes the relationship between members of the subclasses and constraint indicates whether it is possible for a member of a superclass to be a member of one, or more than one, subclass.

What type of database design defines fragmentation and allocation strategies?

Distributed database design

What is an example of a field?

Examples of fields: name, gender, hair color. Think of a person's record for information on Sally. Sally's record is organized by fields. Her name, gender, hair color are all fields that data can be entered into to complete Sally's record.

Cardinality

Expresses the minimum and maximum number of entity occurrences associated with one occurrence of the related entity. EX figure 4.7 the cardinality (1,4) next to CLASS entity in the "PROFESSOR teaches CLASS" relationship indicates that that each professor teachers up to four classes, which means that the PROFESSOR table's primary key value at least once and no more than four times as foreign key values in the CLASS table. Similarly, the cardinality (1,1) next to the PROFESSOR entity indicates that each class is taught by one and only one professor. That is each CLASS entity occurrence is associated with one and only one entity occurrence in PROFESSOR.

Give a data integrity example:

For example, a user could accidentally try to enter a phone number into a date field. If the system enforces data integrity, it will prevent the user from making these mistakes.

What is an example of a subtype discriminator?

For example, these are integer values that are related to its base name. For example, the supertpe is related to a PILOT subtype if the EMP_TYPE has a value of "P." If the EMP_TYPE value is "M," the supertype is related to a MECHANIC subtype.

What is an example of a foreign key?

Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables. For example: In the below example the Stu_Id column in Course_enrollment table is a foreign key as it points to the primary key of the Student table. Think of the project you just submitted. You had to "refer to" the other table to create its foreign key.

Give an example of a database context homonym

In a database context, the word homonym indicates the use of the same name to label different attributes. For example, you might use C_NAME to label a consultant name attribute in a CONSULTANT table. To lessen confusion, you should avoid database homoyms; the data dictionary is very useful in this regard.

What is data integrity?

In a relational database, a condition in which the data in the database complies with all entity and referential integrity constraints.

overlapping subtype

In a special hierarchy, a condition in which each entity instance (row) of the supertype can appear in more than one subtype.

Disjoint constraint

In a specialization hierarchy. these are unique and nonoverlapping subtype entity set.

Information

Information is the result of processing data to reveal its meaning. Accurate, relevant, and timely information is the key to good decision making, and good decision making is the key to organizational survival in a global environment.

What is a PSM?

It stands for persistent storage module. This is a block of code containing standard SQL statements and procedural extensions that is stored and executed at the DBMS server. A PSM lets an administrator assign specific access rights to a stored module to ensure that only authorized users can use it.

Give an example of a superkey vs candidate key.

Let me give you a clear explanation.1. First you have to understand that all the candidate keys are super keys. This is because the candidate keys are chosen out of the super keys.2. How we choose candidate keys from the set of super keys? We look for those keys from which we cannot remove any fields. In the above example, we have not chosen {Emp_SSN, Emp_Name} as candidate key because {Emp_SSN} alone can identify a unique row in the table and Emp_Name is redundant.

What are characteristics of a primary key?

MUST MEET ALL OF THE FOLLOWING: 1. Unique values 2. Nonintelligent 3. No change over time 4. Preferable single-attribute 5. Preferably numeric 6. Security-compliant

Why is metadata important?

Metadata can make finding and working with particular instances of data easier.

What is an example of metadata?

Metadata summarizes basic information about data, which can make finding and working with particular instances of data easier. For example, author, date created and date modified and file size are examples of very basic document metadata

Oracle Sequence

Oracle and SQL server differs slightly in the syntax for retrieving a value from the sequence. In Oracle, you must use two special pseudo-columns: NEXTVAL and CURRVAL. **NEXTVAL retrieves the next available value from a sequence, and CURRVAL retrieves the current value of a sequence.**

Relationships form associations between which database components?

Relationships form associations between entity components.

What is the SQL syntax to do a left out join between two tables?

SELECT select_list FROM T1 LEFT JOIN T2 ON join_predicate;

What is the SQL syntax to list table contents?

SELECT FROM;

Strong and weak entities

Strong entity: this is if an entity can exist apart from all of its related entities, then it is existence independent, and it is referred to as a strong entity or regular entity. The Strong Entity is the one whose existence does not depend on the existence of any other entity in a schema. It is denoted by a single rectangle. A strong entity always has the primary key in the set of attributes that describes the strong entity. Weak entity: It inherits part of its primary key from its strong counterpart. For example, a DEPENDENT can't exist without an EMPLOYEE.

What is overlapping and disjoint contraint do?

The disjoint rule forces subclasses to have disjoint sets of entities. The overlap rule forces a subclass (also known as a supertype instance) to have overlapping sets of entities. Completeness constraints - decide whether a supertype instance must also be a member of at least one subtype.

What is the general definition of overlapping?

The same entity may belong to one or more than one lower level sets.

Synonym

The use of different names to identify the same object, such as an entity, an attribute, or a relationship; synonyms should generally be avoided.

Homonym

The use of the same name to label different attributes. Homonyms generally should be avoided.

Provide more information about homonyms

These are similar-sounding words with different meanings, such as boar and bore.

Disjoint subtypes

These are unique and non-overlapping subtype entity set

What are transitive dependencies?

This exists when there are functional dependencies such that X->Y, Y->Z, and X is the primary key. In that case the dependency X->Z is a transitive dependency because X determines the value of Z via Y.

What are partial dependencies?

This exists when there is a functional dependence in which the determinant is only part of the primary key (remember the assumption, for this disquisition that there is only one candidate key. For example, if (A,B) -> (C,D), B->C and (A,B) is the primary key, then the functional dependence B->C is a partial dependency because only part of the primary key (B) is needed to determine the value of C.

What is an atomic attribute?

This is an attribute that cannot be further subdivided to produce meaningful components. For example, a persons last name attribute cannot be meaningfully subdivided.

What are triggers designed to do?

Triggers are designed to automate business procedures and automatically maintain data integrity and consistency in a modern business environment. For example, one of the most critical business procedures is proper inventory management. For example, you want to make sure that current products sales can be supported with sufficient product availability. Therefor you must ensure that a product order is written to a vendor when that product's inventory drops below its minimum allowable quantity on hand.

Describe the rationale of PK characteristics

UNIQUE VALUES: The PK must uniquely identify each entity instance. A primary key must be able to guarantee unique values. It cannot contain nulls. NONINTELLIGENT: The PK should not have embedded semantic meaning other than to uniquely identify each entity instance. An attribute with embedded semantic meaning is probably better used s a descriptive characteristic of the entity than as an identifier. For example, a student ID of 2093402 would be preferred over Bluer, Marla L. as a primary key identifier. NO CHANGE OVER TIME: If an attribute has a semantic meaning, it might be subject to updates, which is why names do not make good primary keys, if Vickie Jared is the primary key, what happens if she changes her name when she gets married? If a primary key is subject to change, the foreign key values must be updated, thus adding to the database work load. Furthermore, changing a primary key value means that you are basically changing the identity of an entity. In short the PL should be permanent and unchangeable. PREFERABLE SINGLE-ATTRIBUTE: A primary key should have the minimum number of attributes possible . Single attribute primary keys are desirable but not required. Single attribute primary keys simplify the implementation of foreign keys. PREFERABLY NUMERIC: Unique values can be better managed when they are numeric, because the database can use internal routines to implement a counter-style attribute that automatically increments values with the addition of each new row. In fact, most database systems include the ability to use special constructs, such as Autonumber in Microsoft Access, sequences in Oracle or unique identifier in MS SQL Server to support self-incrementing primary key attributes. SECURITY-COMPLIANT: The selected primary key must not be composed of any attributes that might be considered a security risk or violation. For example, using a social security number as a PK in an EMPLOYEE table is not a good idea.

unary, binary, and ternary relationships

Unary relationship: An ER term that exists when an association is maintained within a single entity EX: Employee manages employee Binary relationship: An ER term that exists when two entities are associated (most common) For example, professor teaches class Ternary relationships: An ER term that exists when three entities are associated. This is the rarest type of relationship. For example, DOCTOR writes PRESCRIPTION which is received by PATIENT

What is an example of a sequence?

You could create a sequence to automatically assign values to the customer code each time a new customer is added, and create another sequence or automatically assign values to the invoice number each time a new invoice is added.

Metadata

data that describes other data

In which phase do you install the DBMS?

implementation

Overlapping Constraint

in a partial completeness: supertype has optional subtypes, subtype discriminators can be null, subtype sets are not unique.

Attribute domain

is the set of values allowed in an attribute. For the relational model it is a requirement that each part of a tuple be atomic. The consequence is that each value in the tuple must be of some basic type, like a string or an integer.

Which step does maintenance and evolution fall in the DBLC? (6 steps)(Database Life Cycle)

step 6