INFO 3233 Exam 1 Review Questions

Consider the scenario described by the statement "A customer can make many payments, but each payment is made by only one customer." Use this scenario as the basis for an entity relationship diagram (ERD) representation.

(1:M)

List and describe the different types of databases.

-Single-user database -Multi-user database -Workgroup databases -Enterprise database

What three (often conflicting) database requirements must be addressed in database design?

1. Design standards 2. Information requirements 3. Processing speed

What two courses of action are available to a designer that encounters a multi-valued attribute?

1. Within the original entity, create several new attributes, one for each component of the original multivalued attribute. For example, the CAR entity's attribute CAR_COLOR can be split to create the new attributes CAR_TOPCOLOR, CAR_BODY-COLOR, and CAR_TRIMCOLOR, which are then assigned to the CAR entity. 2. Create a new entity composed of the original multivalued attribute's components. This new entity allows the designer to define color for different sections of the car. Then, this new CAR_COLOR entity is related to the original CAR entity in a 1:M relationship

Discuss two ways in which the 1:M relationship between COURSE and CLASS can be implemented.(hint: weak or strong)

1. weak: use CRS_CODE as FK in CLASS with CLASS having its own PK2. strong: make CRS_CODE composite with another attribute like CLASS_SECTION

What is a business rule, and what is its purpose in data modeling?

A business rule is a brief, precise, and unambiguous description of a policy, procedure, or principle within a specific organization.

What is sparse data? Give an example.

A case in which the number of table attributes is very large but the number of actual data instance is low.

Field (define)

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

File (define)

A collection of related records

What is a composite entity, and when is it used?

A composite entity is generally used to transform M:N relationships into 1:M relationships A composite entity, also known as a bridge entity, is one that has a primary key composed of multiple attributes. The PK attributes are inherited from the entities that it relates to one another.

Discuss the difference between a composite key and a composite attribute. How would each be indicated in an ERD?

A composite key is one that consists of more than one attribute. If the ER diagram contains the attribute names for each of its entities, a composite key is indicated in the ER diagram by the fact that more than one attribute name is underlined to indicate its participation in the primary key.

Multi-user database

A database that supports multiple concurrent users. Supports > 1 User

Single-user database

A database that supports only one user at a time Supports = 1 User

What is a derived attribute? Give an example.

A derived attribute is an attribute whose value is calculated (derived) from other attributes. The derived attribute need not be physically stored within the database; instead, it can be derived by using an algorithm. For example, an employee's age, EMP_AGE, may be found by computing the integer value of the difference between the current date and the EMP_DOB. If you use MS Access, you would use INT((DATE() - EMP_DOB)/365).

What is relational diagram? Give an example.

A graphical representation of a relational database's entities, the attributes within those entities, the relationships among the entities. Figure 2.2

Record (define)

A logically connected set of one or more fields that describes a person, place, or thing.

What is a table, and what role does it play in the relational model

A matrix composed of intersecting rows (entities) and columns (attributes) that represents an entity set in the relational model.

Workgroup databases

A multi-user database that usually supports fewer than 50 users or is used for specific department in an organization. Supports < 50 users

How do you translate business rules into data model components?

A noun in a business rule will translate into an entity in the model, and a verb (active or passive) that associates the nouns will translate into a relationship among the entities.

Describe the basic features of the relational data model and discuss their importance to the end user and the designer.

A relation (sometimes called a table) as a matrix composed of intersecting rows and columns. Each row in relation is called a tuple. Each column represents as attribute. It also describes a precise set of data manipulation constructs based on advanced mathematical concepts. relational model as produced an "automatic transmission" database to replace the "standard transmission" databases that preceded it. Its conceptual simplicity set the stage for a genuine database revolution for both end user and designer.

Briefly, but precisely, explain the difference between single-valued attributes and simple attributes. Give an example of each.

A single-valued attribute is one that can have only one value. (ex. a person has only one first name and only one social security number) A simple attribute is one that cannot be decomposed into its component pieces. (ex. a person's sex is classified as either M or F and there is no reasonable way to decompose M or F)

What is the difference between a database and a table?

A table is a logical structure that represents an entity set. It is only one of the components of a database. The database is a structure that houses one or more tables and metadata. The metadata includes the data characteristics and the relationships between the entity sets.

What is the importance of data models?

A well-developed data model can even foster improved understanding of the organization for which the database design is developed.

What is a recursive relationship? Give an example.

An entity has a relationship with itself ex. An employee is married to an employee

A database user manually notes that "The file contains two hundred records, each record containing nine fields." Use appropriate relational database terminology to "translate" that statement.

An entity set contains two hundred tuples with each record consisting of nine attributes.

What two conditions must be met before an entity can be classified as a weak entity? Give an example of a weak entity.

An entity that displays existence dependence and inherits the primary key of its parent entity. For example, a Dependent requires the existence of an employee.

Why is an object said to have greater semantic content than an entity?

An object-oriented data model (OODM) reflects a very different way to define and use entities. It is described by its factual content and unlike an entity, an object includes information about relationships between the facts within the object, as well as information about its relationships with other objects. Therefore, the facts within the object are given greater meaning. The OODM is said to be a semantic data model because semantic indicates meaning.

Explain why the data dictionary is sometimes called "the database designer's database."

Because it contains metadata relating to the datatypes used and sizes as well has the relations in the database.

What is the role of a DBMS, and what are its advantages? What are its disadvantages?

Better data integration and less data inconsistency. Increased end-user productivity. Improved: Data sharing, data security, data access, decision making Disadvantages: Increased cost, mangement complexity, maintaining currency, vendor dependence, frequent upgrades

What is a DBMS, and what are its functions?

Collection of programs, manages the database structure and controls access to data stored in the database.

What languages emerged to standardize the basic network data model? And why was such standardization important to users and designers?

DML and DLL emerged to standardize the basic data model. This standardization was important to both users and designers because it allowed for the conception of the schema and subschema

What are metadata?

Data about data Describe data characteristics and relationships

What is data independence, and why is it lacking in file systems?

Data storage characteristics is changed without affecting the programs ability to access the data

What does it mean to say that a database displays both entity integrity and referential integrity?

Entity integrity describes a condition in which all tuples with a table are uniquely identified by their primary key. The unique value requirement prohibits a null primary key value, because nulls are not unique. Referential integrity describes a condition i which a foreign key value value has a match i the corresponding table or in which the foreign key value is null. The null foreign key value makes it possible not to have a corresponding, but the matching requirement on values that are not null make it impossible to have an invalid value.

Why are entity integrity and referential integrity important in a database?

Entity integrity is important because it means that a proper search for an existing tuple will always be successful and the failure to find a match on a row search will always mean that the row for which the search is conducted does not exist in that table. Referential integrity is important because its existence ensure that it will be impossible to assign a non existing foreign key value to a table. For example, the referential integrity enforcement in a SALESREP is assigned to CUSTOMER relationship means that it will be possible for a customer to not have a sales rep assigned to him or her but it will be impossible to assign non existing sales rep to a customer.

What is structural independence, and why is it important?

File structure is changed without affecting the applications ability to access the data

Explain why database design is important

Good data modeling facilitates communication between the designer, user, and the developer

Given the business rule "an employee may have many degrees," discuss its effect on attributes, entities, and relationships. (Hint: Remember what a multi-valued attributes and how it might be implemented.

If an employee have many degrees: BA, BS, and MBA. These degrees could be stored in a single string as a multi-valued attribute. The only reasonable solution is to create a new DEGREE entity that stores each degree in a separate record,(There is a 1:M relationship between EMPLOYEE and DEGREE) The DEGREE table's PK is EMP_NUM + DEGREE_CODE. This solution also makes it possible to record the date on which the degree was earned, the institution from which it was earned, and so on.

What common problems does a collection of spreadsheets created by end users share with the typical file system?

Lengthy development times, difficulty, getting quick answers, lack of security, limited data sharing

What are multi-valued attributes, and how can they be handled within the database design?(hint: can have many ________ for a single entity occurrence)

Multi-valued attributes can have many values for a single entity occurrence (car's color)

What is the differences between an object and a class in the object-oriented data model (OODM)?

Object: Object is an entity that can be manipulated by the commands of a programming language such as values, variables , functions or data structure. Class: Class is a construct that is used as a blueprint to create a instance of itself - referred to as a objects.

Explain the difference between data and information.

Raw data (letters, numbers) -->process data = information

Data (define)

Raw facts Not yet been processed to reveal meaning

Define and describe the basic characteristics of a NOSQL database.

Refers to a new generation of database that address the specific challenges of the Big Data era and have the following general characteristics: *Not based on the relational model, hence the name NoSQL *Supports distributed database architectures *Provides high scalability, high availability, and fault tolerance. *Geared toward performance rather than transaction consistency.

Which relational algebra operators can be applied to a pair of tables that are not union-compatible?

SELECT, PROJECT, DIVIDE, PRODUCT, and JOIN

What are the potential costs of implementing a database system?

Skills, implementation, hardware, software, and location

What is connectivity? (Use a Crow's Foot ERD to illustrate connectivity.)

The ER model uses the term connectivity to label the relationship types. The name of the relationship is usually an active or passive verb. Figure 2.3

Explain how the entity relationship (ER) model helped produce a more structured relational database design environment.

The entity relationship model is easier to examine structures graphically than to describe them in text, database designers prefer to use a graphical tool in which entities and their relationships are pictured.

what is an ERDM, and what is the role it does it play in the modern (production) database environment?

The extended relational data model (ERDM) adds many of the OO model's features within the inherently simpler relational database structure.

Enterprise database

The overall company data representation, which provides support for present and expected future needs. Supports > 50 users

Explain why the cost of ownership may be lower with a cloud database than with a traditional, company database.

They are outsourcing their servers. They just pay a company to store their information there as opposed to purchasing their own infrastructures to hold all of their data.

What is data redundancy, and which characteristics of the file system can lead to it?

Unnecessarily storing same data at different places. Islands of information: scattered data locations. -Poor data security -Data inconsistency -Data anomaly

Use examples to compare and contrast unstructured and structured data. Which type is more prevalent in a typical business environment?

Unstructured is in their original state Structured is results from formatting.

What is a strong (or identifying) relationship, and how is it depicted in a Crow's Foot ERD?

When an entity is included into a related entity and contains the primary key from that parent entity. The crow's foot notation depicts a strong relationship with a solid line between the entities.

What is logical independences?

When you can change the internal model without affecting the conceptual model.

What is physical independence?

When you can change the physical model without affecting the internal model.

Explain the significance of the loss of direct, hands-on access to business data that end users experienced with the advent of computerized data repositories.

With direct hands on business data, the users were able to manipulate the organization data. The users were able to create new information with the previous available information. The users wearable to create various tools to retrieve and manipulate company data as per their needs. This creation lead to various reasoning and analysis, that leads to develop various information like, surveys, statistics, reports the were all created by the user.

Describe the Big Data phenomenon.

a movement to find new and better ways to manage large amounts of Web-generated data and derive business insight from it, while simultaneously providing high performance and scalability at a reasonable cost.

What are the main components of a database system?

access languages, and application program interfaces

What are some basic database functions that a spreadsheet cannot perform?

queries

How is a composite entity represented in an ERD, and what is its function? Illustrate the Crow's Foot notation.

the composite entity contains at least the primary key attributes of each of the entities that are connected by it the composite entity can be used to break up M:N relationships into 1:M relationships. ex:STUDENT(/STU_NUM/, STU_LNAME) ENROLL(/STU_NUM/, /CLASS_NUM/, ENROLL_GRADE) CLASS(/CLASS_CODE/, CRS_CODE, CLASS_SECTION, CLASS_TIME, CLASS_PLACE)