MIS 310 Final Exam

What is a 1:M relationship?

1 to many Usually the norm Example: A student enrolls in many courses

What are the 8 rules for a relation to be considered a relation?

1. A table is perceived as a two-dimensional structure composed of rows and columns. (Basically a normal table) 2. Each table row represents a single entity occurrence within the entity set. 3. Each table column represents an attribute and each column has a distinct name. 4. Each row/column intersection represents a single data value. (Also referred to as "Atomic". Data cannot be subdivided into multiple columns, for instance Address can not be subdivided into: House number, zip code, etc. Also it needs to stick to one thing. Like the e-mail column can't hold both work and personal e-mail) 5. All values in a column must conform to the same data format. 6. Each column has a specific range of values known as the attribute domain. (ex. the min and max drop down menu, just something with limited options or within a set) 7. The order of rows and columns is immaterial to the DBMS. (whatever way you order the information like putting phone number before name. Doesn't matter. It'll still make sense. 8. Each table must have an attribute or combination of attributes that uniquely defines each row. (Primary key)

What are the 5 Major Components of the Database System Environment?

1. Hardware 2. Software 3. People 4. Procedures 5. Data

What are the 3 stages of database development?

1. Requirements Analysis (Business Rules) 2. Design (ERD) 3. Implementation

What is a ternary relationship?

3 entities are associated

For most business database design purposes, which normalization form is as high as needed?

3NF, is usually as high as needed. 2NF is better than 1NF; 3NF is better than 2NF Highest level of normalization is not always the most desirable. As normal form increases, performance decreases.

What is a nested query?

A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. Basically if query results require two or more steps, nested queries are used.

What is a primary key?

A candidate key chosen to uniquely identify all other attribute values in any given row. If you know the value of the primary key, you will be able to uniquely identify a single row. The attribute you choose to be your Primary Key cannot contain null entries.

What is an entity class?

A collection of entities which provides the structure of the entities in that class. ex: MIS310 is an instance of the entity class "Course" ex: John Doe is an instance of entity class "Student"

What is a computed column?

A column that does not physically exist in the database but is computed using data in other columns.

What is a query?

A command to retrieve data from tables "A question represented in a way the DBMS can understand"

What is a data warehouse?

A data warehouse is a system that pulls together data from many different sources within an organization for reporting and analysis. The reports created from complex queries within a data warehouse are used to make business decisions.

What is a file system and where is it still used?

A file system is a collection of file folders each labeled and organized by alphabetical order and stored in a cabinet (not digital) mainly used before SQL and stuff but some place still do. Ex. Doctor's Office, Folder Structure on your computer, etc.

What is a having clause?

A having clause specifies a condition that needs to be satisfied by a group resulting from a group by clause in order to be settled. ex. You could group by color, but only colors having a count in inventory less than 5.

What is a key and a little bit about it?

A key is one or more attributes that determine other attributes. If a key consists of two or more attributes, it is called a composite key. A key's role is based on the "principle of determination" - if you know the value of attribute A, you can determine the value of attribute B. (Knowing the G-Number, you can look up students information)

What is a composite key?

A key that contains two or more attributes For unique identification of a row, often it is necessary to use a composite key to identify each row uniquely.

What is a query language?

A non procedural language. Which allows the user to specify what must be done without specifying how it msut be done.

What is a composite identifier?

A primary key composed of more than one attribute. A composite identifier is one that consists of two or more attributes. Ex: EmployeeNumber & LineItemNumber are both required

What is cardinality?

A relationships count. So like putting in parenthesis by it (1,N) for 1 to many or (1,7) to show a maximum cardinality of 7 and minimum of 1.

How could you differentiate a strong/weak relationship?

A strong relationship with a solid line. (If PK of related entity has PK of parent entity) A weak relationship with a dotted line. (If PK of parent entity is not in related entity)

What is a relationship?

A table related to another table. Ex: An employee works in a department. Think of ERD

What is a theme?

A theme is a different word for table. Basically a topic about which data is being stored.

What is a surrogate key?

A unique, made up value that is added to a relation to serve as the primary key. They have no meaning to business users. They're often created to replace a composite primary key, the original primary key has privacy/security implications, or the primary key column is difficult to verify.

What is entity integrity?

All primary key entries are unique and no part is null.

What does a Full Outer Join show?

All rows from both tables will be included regardless of matches.

What does a Left Outer Join show?

All rows from the table on the left (listed first in the query) will be included; matching rows only from the table on the right will be included.

What does a Right Outer Join show?

All rows from the table on the right (listed second in the query) will be included; matching rows only from the table on the left will be included.

What does the join relational set operator do?

Allows information to be combined from two or more tables The most common joins are: Natural (inner) join - Links tables by selecting rows with common values in common attribute(s) Outer join - Lists rows from one of the tables even when there are no matching values in the other table. Missing values in the second table are set to null.

What is 4GL? (4th Generation Language)

Allows the user to specify only what must be done in a natural language.

What is an alias?

An alternative column name that can be created for a computed column. For instance you could use the concat function and combine First Name and Last Name under an alias known as "Customer Name"

What is a candidate key?

An attribute (or combination of attributes) that is able to help uniquely identify a row. This key should be able to become the primary key of a table. Examples: GMU Email G-Number Mobile Phone Number

What is a secondary key?

An attribute (or combination of attributes) used strictly for data retrieval purposes. Example: Description columns, contact information, etc.

What is an entity instance?

An entity instance of an entity class is the occurrence of a particular entity.

What is a unique index?

An index in which the index key can have only one pointer value (row) associated with it.

What is an index key?

An index's reference point. Points to data location identified by the key. (Painter number in ex. which would point to painting table rows)

What are indexes?

An orderly arrangement to logically access rows in a table. Index helps retrieve the data quicker, if implemented correctly. (each index is associated with only one table)

What is a unary relationship?

Association is maintained within a single entity Also called a recursive relationship

What is a relation?

Basically a table, however it's in a database and adheres to 8 rules.

What is a composite attribute?

Can be subdivided ex: person's name, address

What does select do in relational algebra?

Can be used to list either all row values or it can yield only those row values that match a specified criterion.

What is a multi-valued attribute?

Can have many values (not allowed in relational models) Example: Phone numbers, degrees earned To resolve this problem, create a new entity composed of original multi-valued attribute's components. Example: Instead of Employee_Name, use _Employee_FName and Employee_LName

What is a single-value attribute?

Can have only a single value Example: G Number, SSN, date of birth

What is a simple attribute?

Cannot be subdivided ex: gender, last name, first name

What does union do in relational algebra?

Combines all rows from two tables, excluding duplicate rows.

What is a compound condition?

Connects two or more simple conditions with logical operators. AND operator OR operator NOT operator

What are the two broad categories of language all SQL functions fit into?

Data Definition Language (DDL): Which includes commands to create database objects (e.g. tables, indexes, views, etc.) and define access rights to those databases. ^ CREATE, ALTER, DROP, TRUNCATE ^ Data Manipulation Language (DML) Which includes commands to insert, update, delete, and retrieve data within database tables. ^ INSERT, UPDATE, DELETE ^

What is metadata?

Data about data Provides description of data characteristics and relationships in data. Complements and expands value of data Stored separately in "system" tables

What is a database system?

Defines and regulates the collection, storage, management, and use of data. Consists of logically related data stored in a single logical data repository. The purpose is to keep track of things. Unlike a list or spreadsheet, a database may store information that is more complicated than a simple list

What is relational algebra?

Defines theoretical ways of manipulating table contents using relational operators. The relational model supports the following relational algebra operators: Select, project, join, intersect, union, difference, product, divide.

What is the system catalog?

Detailed system data dictionary that describes all objects within the database, such as tables, indexes, views, stored procedures, etc.

What are some key bullet points for showing a database is successfully normalized?

Each table represents a single subject/theme No data item will be unnecessarily stored in more than one table All non prime attributes in a table are dependent on the primary key Each table is void of insertion, update, and deletion anomalies.

What is existence independence?

Entity can exist apart from one or more related entities Sometimes such an entity is referred to as strong or regular entity

What is existence dependence?

Entity exists in database only when it is associated with another related entity occurrence. Example: DEPENDENT entity is existence-dependent on EMPLOYEE

What are some issues to address for normalization? (Don't have to list all, just as general help)

Evaluate PK Assignments Evaluate Naming Conventions Refine Attribute Atomicity Identify New Attributes Identify New Relationships Refine primary keys as required for data granularity Maintain historical accuracy Evaluate using derived attributes.

What are the series of stages for normalization that we need to know for this class?

First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF) Fourth Normal Form (4NF) <--we don't use in class

What is the main source of data anomalies?

Functional dependencies Goal: All attributes are fully dependent on the primary key

What are lists and what are some issues with lists?

In a list, each row is intended to stand on its own. Lists are still used to day. For example in Microsoft Excel and Sharepoint. Some issues with Lists are: Retrieval Redundancy: A list of projects could include a lot of Project Manager Information (Name, ID, Phone Extension, etc.) and Project Information in the same row. If one particular person was managing 10 projects, then his/her information would have to be entered 10 different times. Multiple Themes: In a list, each row may contain information on more than one theme. As a result, needed information may appear in the lists only if information on other themes is also present. These will also lead to Modification Issues: Deletion Problems, Update Problems, and Insertion problems.

What are the steps for conversion to 1NF?

Step 1: Eliminate the repeating groups Step 2: Identify the primary key Step 3 Identify all dependencies

What are the steps for conversion to 3NF?

Step 1: Identify each new determinant For every transitive dependency, write its determinant as PK for the new table. Step 2: Identify the dependent attributes Identify attributes dependent on each determinant identified in step 1. Step 3: Remove the dependent attributes from transitive dependencies. Eliminate all dependent attributes in transitive relationship(s) from each of the tables.

What are the steps for conversion to 2NF?

Step 1: Write each key component on a separate line Step 2: Assign corresponding dependent attributes

What happens during Stage 1, Requirements Analysis?

System users are interviewed Sample forms, reports, queries and descriptions of update activities are obtained. Sources of requirements: User interviews, forms, reports, queries, use cases, business rules.

What is a composite/bridge entity?

Used to split up a M:N relationship into 2 separate segments. You need to take the primary keys of the first two tables and put them as a foreign key for a new table. Therefore they're both pk and fk. It will be called a bridging entity and combine the two.

What is a derived attribute?

Value may be calculated from other attributes. Need not be physically stored within database Example: Employee's age can be derived from Date of Birth.

What is the model we use in our class? What is the other one we should know but not use?

We use the Crow's Foot Model The other one is the Chen Model

What is a partial dependency?

When an attribute can stand alongside with part of the primary key, then that's what defines a partial dependency. Unless you don't have a primary key. (Only occur when a table has a composite key)

What's a transitive dependency?

When it's dependent on another attribute which is not the primary key.

What does product do in relational algebra?

Yields all possible pairs of rows from two tables

What does difference do in relational algebra?

Yields all rows in table 1 not found in table 2

What does project do in relational algebra?

Yields all values for selected attributes

What does intersect do in relational algebra?

Yields only the rows that appear in both tables

What is an Ad Hoc Query?

Something created in the spur of the moment as the need arises. (Could not be done before databases)

What is an entity?

Something of importance to a user that needs to be represented in a database.

What is relationship degree?

Indicates the number of entities or participants associated with a relationship.

What are the 4 types of JOINS?

Inner Join Left Right Full

What are the two classifications of joins that the 4 types fit under?

Inner Joins Merge rows from two tables and return the rows that have common values in common columns. Outer Joins Merge rows from two tables and return the rows that have common values in common columns or have no matching values. Full, Left & Right

What is the WHERE clause?

It is used to select partial table contents by placing restrictions on rows to be included in output. (=,<,>,<=,>=,<>,!=) <-- Example of comparison operators

What are null values?

It means that in that column there was no data entered for that attribute. It means "No data entry", a missing value, or not applicable. It does not mean a blank space or a 0.

What is one of the most important advantages of the relational database management system (RDBMS)?

Its ability to hide the complexities of the relational model from the user.

What is a JOIN?

Joins are used to link tables and retrieve data from two or more tables using foreign key references. The result is a single table with all columns from joined tables. Joins can be created through: Conditions in a WHERE clause (Old Style) Use of join keywords in FROM clause.

What is a M:N relationship?

Many to Many Cannot be implemented as such in the relational model M:N relationships can be changed into 1:M relationships with a composite / bridge entity Example: Many students take many courses

What is an identifier?

One or more attributes that uniquely identify each entity instance. Entity instances have identifiers. An identifier is an attribute that names or identifies instances such as: SocialSecurityNumber, StudentID; & EmployeeID Identifiers may be unique or nonunique If the identifier is unique, the data value for the identifier must be unique for all instances.

What are business rules?

Precise descriptions of policies, procedures, or principles in any organization that stores and uses data to generate information Represents detailed descriptions of policies, procedures, or principles within a specific org. Example 1: A customer may generate many invoices. An invoice is generated by only one customer. Basically the rule given to use when making the relationship for ERD.

What is the normalization process? (List)

Progressively breaks a table into a new set of relations (tables) based on identified dependencies Each table represents a single subject No data item will be unnecessarily stored in more than one table All attributes in a table are dependent on the primary key Each table void of insertion, update, deletion anomalies

What is a data dictionary?

Provides detailed accounting of all tables found within the user/designer-created database. Contains (at least) all the attribute names, data types, and characteristics for each table in the system. Contains metadata

What is Referential Integrity?

States that every value of a foreign key must match a value of an existing primary key. For Example: If an employee has a DeptID = 7 (Foreign Key), a Department with DeptID = 7 (primary key) must exist in Department (table).

What is data administration?

Refers to a function that applies to an entire organization concerning corporate data privacy and security issues.

What is database administration?

Refers to a more technical function that is specific to a particular database, including applications associated with it.

What is a data model?

Represents the content, relationships and constraints of the data needed to support the requirements. (basically what we worked on for ERD hw 1)

What is 3GL? (3rd Generation Language)

Requires the programmer to specify what AND how a task is to be done.

What is the de facto query language?

SQL (Structured Query Language) which is supported by a majority of DBMS vendors It is a 4GL (4th Generation Language)

What is a constraint?

SQL Constraints are used to specify rules for the data in the table. They are used to limit the type of data that can go into a table.

What is a self join?

Self-join is set to be query used to compare to itself. This is used to compare values in a column with other values in the same column in the same table. ALIAS ES can be used for the same table comparison. It is just joining a table to itself

What is a 1:1 relationship?

Should be rare in any relational database design Example: A professor is the chair of a department

What is a DBMS (Database Management System)

The intermediary between the user and the database. Most DBMS functions are transparent to end users and can only be achieved through the DBMS. A database management system (DBMS) is a software package designed to define, manipulate, retrieve and manage data in a database. A DBMS generally manipulates the data itself, the data format, field names, record structure and file structure. It also defines rules to validate and manipulate this data.

In a 1:M relationship, which side would the many key always be on?

The many side.

What is normalization?

The process for evaluating and correcting table structures to reduce data anomalies

What is a foreign key?

This is to connect and preserve relationships. The primary key of one relation is placed into a second relation as a foreign key to represent a relationship. Foreign keys are represented in a relationship by italics. Foreign keys help control data redundancies by using common attributes shared from tables and making controlled redundancies.

How are unstructured and semi structured data processed?

Through a new generation of databases. XML. (Extensible Markup Language: Represents data elements in textual format (web page content, e-mails, etc.)

Why do people need to store and organize data?

Tracking purposes like inventory, weather statistics, etc.

What is a binary relationship?

Two entities are associated