CIT170 Chapters 1-5

How many optional columns will the relational schema mapped from the ABC Retailer ER diagram have? 0 1 2 3 4

0

How many tables in the relational schema mapped from the Eaglerun Trucking Dispatcher ER diagram will have composite primary keys? 0 1 2 3 4

1

If an entity has two unique attributes, how many primary keys will its mapped relation have? 0 1 2 3 4

1

If one table had the following attributes, how many tables should it be normalized into? ClientID ClientName ClientRegion CampaignID CampName CampLength 0 1 2 3

2

Observe the ER diagram for the CENTRAL SCHOOL LIBRARY. How many columns will the relation LIBRARY BOOK have in the relational schema mapped from the Central School Library ER diagram? 4 6 3 5 2

2

How many columns will the PROMOTION table have in the database from the ABC Retailer ER diagram? 2 3 4 5 6

3

Observe the ER diagram for the ABC Retailer. How many entities are represented from the ABC Retailer ER diagram have? 3 1 5 4 2

3

Observe the ER diagram for the Eaglerun Trucking Dispatcher. How many columns will the relation DRIVER have in the relational schema mapped from the Eaglerun Trucking Dispatcher ER diagram? 1 3 0 4 2

3

How many columns will a relation resulting from mapping the entity Employee have? 3 6 5 2 8

6

Which of the following is a part of a DDL statement? CREATE INSERT SELECT DELETE

CREATE

Show the CREATE TABLE statements for the table AREA (assume that non-primary key columns can be optional.) HAPPY INSURANCE Observe the HAPPY INSURANCE DATABASE: CLIENT ClientID ClientName ClientAgent ClientSpouseName C111 Tom A1 Jenny C222 Karin A1 Bill C333 Cole A2 Amy C444 Dorothy A2 C555 Andy A3 Amy C666 Tina A3 Matt C777 Christina A4 Mike AGENT AgentID AgentName AgentArea AgentRating AgentYearOfHire SupervisedBy A1 Kate 1 101 1990 A2 Amy 2 92 2009 A1 A3 Luke 3 100 1992 A4 James 3 90 2010 A3 AREA AreaID AreaName AreaHQ 1 East Boston 2 West San Francisco 3 Central Chicago CREATE TABLE area (areaid CHAR(2), areaname CHAR(10), areahq CHAR(2), PRIMARY KEY (areaid)); CREATE TABLE area (areaid CHAR(1), areaname CHAR(10), areahq CHAR(20), PRIMARY KEY (areaid); CREATE TABLE area (areaid CHAR(1), areaname CHAR(1), areahq CHAR(20), PRIMARY KEY (areaid)); CREATE TABLE area (areaid CHAR(1), areaname CHAR(10), areahq CHAR(20), PRIMARY KEY (areaid));

CREATE TABLE area (areaid CHAR(1), areaname CHAR(10), areahq CHAR(20), PRIMARY KEY (areaid));

What is the purpose of DML statements? none of these answers DML statements are used to manipulate the data within the database. DML statements are used to maneuver the data within the database. DML statements are used to manange the data within the database.

DML statements are used to manipulate the data within the database

Joe is an example of: -Data -Metadata -Data Type -DBMS -All of the above

Data

Autonumber data type option: Automatically converts the existing data type into a number type Assigns a number to each of the used data types Enables automatic counting of columns in a table Enables automatic counting of tables in a database Enables automatic generation of consecutive numeric data values in a column

Enables automatic generation of consecutive numeric data values in a column

A required field may have a null value attached to it. (T/F)

False

Observe the table VEHICLE that contains the following four records: VEHICLE VehicleID Type Make OwnerID OwnerName 111 Car Ford O1 Pam 222 Car Ford O2 Pat 333 Truck Honda O3 Pam 444 Car Honda O1 Pam The following query on table VEHICLE will return 3 values: SELECT vehicleid FROM vehicle; (T/F)

False

The logical database model is created prior to the conceptual database model. (T/F)

False

Update operations are also referred to as read operations. (T/F)

False

Within one relation, two rows can be identical.(T/F)

False

What is the result of the following SQL query? SELECT ename, esalary * 1.1 FROM employee; For each employee, the query will display the name and 10% of the salary For each employee, the query will display the name and the salary increased by 10% For each employee, the query will display the name, the salary, and all other columns from the table employee For each employee, the query will display the name, the salary, all other columns from the table employee, and the number 1.1

For each employee, the query will display the name and the salary increased by 10%

When is the IS NULL comparison used? IS NULL comparison is used in queries that contain additions with an empty value in a column of a record IS NULL comparison is used in queries that contain comparisons with an empty value in a column of a flat file IS NULL comparison is used in queries that contain comparisons with an empty value in a column of a record IS NULL comparison is used in queries that contain comparisons with an empty value in a column of a tuple

IS NULL comparison is used in queries that contain comparisons with an empty value in a column of a record

Which of the following is an objective of selecting a data type? Maximize storage space Represent a small number of possible values Limit security Improve data integrity

Improve data integrity

Which of the following (regarding the relation ENROLLMENT) represents the insertion anomaly? ENROLLMENT StudentID StudentName MajorID MajorName 111 Joe E English 222 Bob H History 333 Lisa H History Inability to insert a student without adding a major Inability to insert a major without adding a student Inability to insert a major name without a major ID Inability to insert a student name without a student ID

Inability to insert a major without adding a student

Front-end applications facilitate: -Direct interaction -Indirect interaction -Both direct and indirect interaction -Neither direct nor indirect interaction -Sometimes direct and sometimes indirect interaction (but not both at the same time)

Indirect Interaction

Which of the following is an example of analytical information? -Information about the amount of money in a checking account -Information showing which airline routes in the United States have the most sales -Information about the cost of a product -Information showing the destination and origin of a particular airline route in the United States -Information about the age of a student

Information showing which airline routes in the US have the most sale

Update operations can be labeled as: Insert, Delete, Modify Insert, Delete, Change Add, Remove, Change Add, Remove, Modify Insert, Remove, Modify

Insert, delete, modify

Database implementation involves using ___X___ to implement ___Y___ as an actual database. In the above sentence Y should be replaced with -Front end application -Conceptual database model -Logical database model -DBMS -Any of the above

Logical database model

Anomalies are possible due to what type of data? Multiple Single Redundant Repeated Composite

Redundant

Tuple is a synonym for: Relationship Column Field Row Table

Row

Write the SQL query on the table VEHICLE that lists once the owner ID and owner name of each owner: Observe the table VEHICLE that contains the following four records: VEHICLE VehicleID Type Make OwnerID OwnerName 111 Car Ford O1 Pam 222 Car Ford O2 Pat 333 Truck Honda O3 Pam 444 Car Honda O1 Pam SELECT DISTINCT ownerid, ownername FROM vehicle; SELECT DISTINCT owner, ownerfname FROM vehicle; SELECT ownerid, ownername FROM vehicle; SELECT DISTINCT ownerid, ownername FROM vehicle

SELECT DISTINCT ownerid, ownername FROM vehicle;

Which of the following is an invalid SQL keyword? SE_LECT Select SeLeCt selecT

SE_LECT

Which of the following is a part of a DML statement? ALTER CREATE DROP SELECT

Select

Which of the following functional dependencies does NOT exist in the table ENROLLMENT? StudentID StudentName MajorID MajorName 111 Joe E English 222 Bob H History 333 Lisa H History StudentName --> StudentID StudentID --> MajorID MajorID --> MajorName StudentID --> MajorName StudentID --> StudentName

StudentName --> StudentID

What is the result of the following SQL query? SELECT ename FROM employee WHERE esalary = (SELECT MAX(salary) FROM employee); The query displays the salary of the employee (or employees) with the highest salary The query displays the number that represents how many employees share the highest salary The query displays the name of the employee (or employees) with the highest salary The query displays the name and the salary of the employee (or employees) with the highest salary

The query displays the name of the employee (or employees) with the highest salary

If a 1:M relationship is mandatory on both sides, which of the following is true? The resulting foreign key can have null (empty) values The resulting foreign key cannot have null (empty) values The resulting foreign key must have null (empty) values No foreign key is created as a result of mapping such a relationship Two foreign keys are created as a result of mapping such a relationship, one that can have null (empty values) and one that cannot have null (empty values)

The resulting foreign key cannot have null (empty) values

What is the result of the following SQL statement? UPDATE employee SET salary = 50000 WHERE salary < 50000; The statement sets the salary of each employee to 50,000 and ensures that the salary cannot drop below 50,000 The statement increases salary to 50,000 for each employee whose salary is below 50,000 The statement sets the salary for each employee to below 50,000 The statement sets the salary of each employee to 50,000

The statement increases salary to 50,000 for each employee whose salary is below 50,000

Every relation contains: Equivalent functional dependency Augmented functional dependency Trivial functional dependency All of these None of these

Trivial functional dependency

A partial functional dependency can occur only in cases when a relation has a composite primary key (T/F)

True

An entity relationship diagram is a visual depiction of the relational database model.(T/F)

True

Database administration encompasses the tasks related to the maintenance and supervision of a database system. (T/F)

True

Every attribute appears only once in an ER diagram. (T/F)

True

In many cases, when viewing a table diagram, the easiest way to tell if it needs to be normalized is if it has more than one ID field name? (T/F)

True

The term data refers to facts that are recorded and can be accessed. (T/F)

True

The term information refers to the data that is accessed by a user for some particular purpose. (T/F)

True

Two columns of a relational table can have the same values. (T/F)

True

Two columns of a relations table can have same names. (T/F)

True

Which of the following is NOT true? In a relational database: Two tables can be a part of the same database Two tables, in the same database, can have the same name Two tables, in the same database, can have the same number of columns Two tables, in the same database, can have the same number of rows A foreign key column of one table is related to a primary key column of another table

Two tables, in the same database, can have the same name

Which of the following statements would be used to change a value in a record of a table? INSERT INTO UPDATE ALTER TABLE CREATE TABLE

Update

____________ the first letter of each word in a field name that combines multiple words. capitalize none of these answers underscore make lower case

capitalize

___________ a database rearranges the data and objects in a database to decrease its file size, thereby making more storage space available and enhancing the performance of the database compacting viewing analyzing repairing

compacting

Give fields ___________ names so that you can easily identify them when you view or edit records detailed vague short descriptive

descriptive

Two ways you can created fields in Access are in either _____________ or _____________ view. design or datasheet design or developer developer or datasheet developer or create

design or datasheet

What is the purpose of a ";" (semicolon) in the SQL statement? indicates the continuation of the SQL command none of these answers indicates the beginning of the SQL command indicates the end of the SQL command

indicates the end of the SQL command

Forms display ________ record(s) at a time one all zero two

one

Which of the following is a read operation? Entering new data in the relation Retrieving data from the relation Removing data from the relation Changing the existing data in the relation All of these

retrieving data from the relation

Within databases, why do we try to eliminate anomalies? none of these because labor costs are very expensive and we must limit unnecessary costs of repeat adjustments same modification has to be made multiple times in different areas database administrator wants to ensure job security

same modification has to be made multiple times in different areas

A field name cannot begin with a __________. number special character space lowercase letter

space

Although allowed in Access, experienced database developers avoid using _______ because they can cause errors when the objects are involved in programming tasks. Selected Answer: dashes special characters underscores spaces

spaces

In Access, you press the _____ or _____ key to move from one field to the next ctrl or enter alt or enter tab or ctrl tab or enter

tab or enter

A __________ is a collection of fields that describes a person, place, object, event, or idea table database data file field

table

Relation is a synonym for the following construct in a relational database Relationship Column Field Row Table

table

What is the purpose of the JOIN operation? used in SQL to facilitate the querying of multiple tables used in SQL to facilitate the querying of multiple columns used in SQL to facilitate the querying of multiple files used in SQL to facilitate the querying of multiple databases

used in SQL to facilitate the querying of multiple tables

The Back Up Database command is completed by following these steps: •Click the Options tab to display the Info screen in Backstage view •Click Save As in the navigation bar •Click Compact Database in the Advanced section of the Save Database As pane •Click the Save As button •Click the File tab to display the Info screen in Backstage view •Click Save As in the navigation bar •Click Back Up Database in the Advanced section of the Save Database As pane •Click the Save As button •Click the File tab to display the Info screen in Backstage view •Click Save As in the navigation bar •Click Compact Database in the Advanced section of the Save Database As pane •Click the Save As button •Click the Options tab to display the Info screen in Backstage view •Click Save As in the navigation bar •Click Back Up Database in the Advanced section of the Save Database As pane •Click the Save As button

•Click the File tab to display the Info screen in Backstage view •Click Save As in the navigation bar •Click Back Up Database in the Advanced section of the Save Database As pane •Click the Save As button

If a 1:M relationship is optional on both sides, which of the following is true? The resulting foreign key can have null (empty) values The resulting foreign key cannot have null (empty) values The resulting foreign key must have null (empty) values No foreign key is created as a result of mapping such a relationship Two foreign keys are created as a result of mapping such a relationship, one that can have null (empty values) and one that cannot have null (empty values)

The resulting foreign key can have null (empty) values

Every relation contains: Full key functional dependency Partial functional dependency Transitive functional dependency All of the above None of the above

Full key functional dependency

transaction

any business activity central to the nature of the enterprise (sale of a product, flight of an airliner, college student course grade, etc.)

Optional attribute

attribute that is allowed to not have a value

When you start Access, the first screen that appears is ___________ which contains commands that allow you to manage Access files and options current view rear view initial view backstage view

backstage view

Table

How data is stored for an entity Columns of the table are attributes the rows (aka records) are the specific instances of that entity

Fields are also called keys in a database. Which key is the most important since it contains a unique value for each row (or record) of data? primary field maximum query

primary

A _____________ is a question you ask about the data stored in a database report form table query

query

Mapping weak entities

•Weak entities are mapped in a same way as regular entities with one addition: oThe resulting relation has a composite primary key that is composed of the partial identifier and the foreign key corresponding to the primary key of the owner entity

Database use

the insertion, modification, deletion and retrieval of the data in the database system

ER diagram (ERD)

the result of ER modeling Serves as a blueprint for the database

Business rules

•User defined constraints that specify restrictions on databases that are not a part of the standard notation for creating ER diagrams

Foreign Key

A foreign key is a column in a relation that refers to a primary key column in another (referred) relation.

Entity instances (entity members)

Occurrences of an entity. Entities themselves are depicted in the ER diagrams while entity instances are not. Entity instances are eventually recorded in the database that is created based on the ER diagram.

unary relationship (recursive relationship)

Occurs when an entity is involved in a relationship with itself. (degree 1 relationship)

Database implementation involves using ___X___ to implement ___Y___ as an actual database. In the above sentence X should be replaced with -Front-end applications -Database system -Indirect interaction -DBMS -Any of the above

DBMS

Database users

Data entry operators type data into databases and make sure that it is accurate. They - add records - modify records - delete records - sort records

Relationship

ER modeling construct depicting how entities are related •Within an ER diagram, each entity must be related to at least one other entity via a relationship

DATABASE REQUIREMENTS AND ER MODEL USAGE

ER modeling provides a straightforward technique for collecting, structuring, and visualizing requirements An understanding of ER modeling is crucial, not just for creating ER models based on the requirements, but also during the requirements collection process itself It helps keep the focus on asking or seeking answers to the right questions in order to establish the relevant facts about entities, attributes, and relationships One of the common mistakes that beginners make when engaging in ER modeling for the first time is not recognizing the difference between an entity and the ER diagram itself Another common database requirements collection and ER modeling mistake made by novices is not distinguishing between: Modeling of the data that is wanted and can be kept track of versus Modeling of everything that takes place in an organization

Cardinality constraint

The lines connecting the relationship to the entities include symbols depicting how many instances of one entity can be associated with instances of another entity. These symbols are known as cardinality constraint

Data CAN be imported into Access from other sources (T/F)

True

It's best to rename the primary key ID field instead of leaving as the default ID name. (T/F)

True

databases help turn 'data' into useful 'information' (T/F)

True

A field is ____________ or attribute of a person, place, object, event, or idea none of these answers information a single characteristic the largest piece of data

a single characteristic

date/time

allows only valid times/dates, such as Nov 12, 2007

Multimedia Databases

allows storage of pictures, movies, sounds, and hyperlinked fields

Common field data types

alphanumeric, numeric, logical, currency, memo, object, hyperlink, date/time, sequence (or auto-number)

optional attribute

attribute that is allowed to not have a value, marked with a letter "o"

Which of the following terms is defined as: information about data, such as field length, data type, etc., describes various elements of data - Metadata - polydata - databank - unidata

metadata

What is depicted by ER Diagrams? Data Only Metadata Only Both data and metadata Neither data nor metadata Either data or metadata, but not both

metadata only

Based on the ERD below, how many pets can a vet have under his/her work: minimum of 1, maximum of 50 minimum of 1, maximum of 2 minimum of 10, maximum of 50 as many as possible

minimum of 10, maximum of 50

Databases are implemented using relational DBMS (RDBMS)

most modern databases are modeled as relation databases

object

nontextual information such as pictures

Multiple unique attributes (candidate keys)

when an entity has more than one unique attribute each unique attribute is also called a candidate key

Designer-created primary key

-primary key column, not called for by the original requirements, added to a table by the database designer •Often used in conjunction with the autonumber data type option

Information

-refers to the data that is accessed by a user for some particular purpose •Typically, getting the needed information from a collection of data requires performing an activity, such as searching through, processing, or manipulating the data in some form or fashion

Primary Key

An attribute that uniquely identifies each instance of that entity 1. Must be unique to each person/entity 2. Every person/entity must have one 3. cannot be shared amongst more than one person/ entity

According to the ERD below, which Pet attribute is multivalued? Color Pet ID Breed Age

Color

Only __________ primary keys have separate components Unique Multivalued Dual Composite Derived

Composite

Which of the following is a modify operation? Entering new data in the relation Retrieving data from the relation Removing data from the relation Changing the existing data in the relation All of these

Changing the existing data in the relation

Data vs Information

-Data - raw, unorganized content in the form of words, numbers, sounds, or images -Information - data associated with other useful data on the same topic -REMEMBER that databases help turn 'data' into useful 'information'

Data Loss or Corruption

-Data corruption occurs when data is unreadable, incomplete, or damaged. -Backing up data is a major method for recovering lost or corrupted data.

Levels of data within a data base

-Entity - a person, place, thing, or event (examples include sales transaction, student grades, traffic violations, telephone records) -Field - smallest element of data in a database. A single value, such as a name, address, or dollar amount •data type: usually numeric or text (numbers and text) •name: assigned by person developing the database •size: number of characters that can be entered -Record - related fields describing an event or situation (would include fields such as name, address, city, state, zip, etc.) -File - collection of records of the same type

Relational Databases

-Fields can be shared among all files in the database, making it possible to connect them. -A file is called a table (consists of rows and columns). -A record is called a tuple. A field is called an attribute

ER AND RELATIONAL MODELING

-Process of requirements collection should be accompanied by the ER modeling and then followed by mapping the ER model into a subsequent relational schema §Some practitioners prefer to create relational schemas straight from the requirements In such cases, the ER modeling phase is simply omitted -Create relational schemas straight from the requirements is not advisable for following reasons •ER modeling is more suited for visualization of the requirements •Certain concepts can be visualized graphically only in ER diagrams •Every attribute is mentioned only once in the ER diagram •An ER model is a better communication and documentation device

Query Tools (Search Tools)

-Query tools help users narrow down information needed to be searched. -A query allows users to ask questions designed to retrieve information. -A select command asks a database to return records that match specific criteria. Joining matches data from fields in various files

Backup and Recovery Operations

-backups commonly used because they are cheap and easy to store -backups must be stored separately from original material to lessen accidental data loss

Database Objects: Tools in the DBMS

-form - template that allows users to enter data into the database (established to make data entry easier) -report - formatted body of output from a database -data filter - filtering material in a database to restrict a database search

metadata

-information about data; describes significance of various elements of a database (If a record is named FNAME, the metadata might describe this as being a text field with a max of 20 characters used for holding a person's first name)

Database Administrator

-is responsible for maintaining and updating the database and the DBMS software -is responsible for preventing computer downtime

transactional processing

-more continuous • real time system - data must be accurate to the second • online transactional processing - very fast, always-on processing (includes time sensitive databases such as medical, air traffic control, etc.)

batch processing

-occurs at a scheduled time or when a critical point has been reached (data may only be updated at midnight each day)

Management Information Systems (MIS)

-used to track and control every transaction through a database

3 things data bases are composed of

1. Entities, 2. Attributes, 3. Relationships

How many entities are involved in a binary relationship? 1 2 Between 1 and 2 More than 2 More than 3

2

How many entities are depicted by the following requirements? School XYZ keeps track of its 100 students, 10 teachers, and 5 classrooms. 116 4 115 none of these answers 3 1005

3

The guidelines and methods for database requirements phase call for an iterative process

A smaller beginning set of requirements can be collected, defined, and visualized, and then discussed by the database developers and intended end users. These discussions can then lead into another iteration of collecting, defining, and visualizing requirements that gradually increases the first set of requirements.

According to the ERD below, which attribute under Vet can be blank or null? Awards Won Vet ID Vet Full Name Year of Service

Awards Won

Which of the following: allows managers to work with "what if" scenarios? - Decision Support Systems - Management Support Systems - Factory Automation Systems - none of these answers

Decision Support Systems

entity-relationship Modeling

ER Modeling

NAMING CONVENTIONS FOR ER DIAGRAMS

Entities and attributes •Use singular (rather than plural) nouns Relationships Use verbs or verb phrases, rather than noun Names should be as brief as possible, without being too condensed as to obscure the meaning of the construct If possible, give all attributes in the entire ER diagram different names

A foreign Key column must have the same name as the primary key column it refers to. (T/F)

False

Data and information are the same. t/f

False

An associative entity is used as an alternative way of depicting: 1:M relationships 1:1 relationships M:N relationships Multivalued attributes Derived attributes

M:N relationships

Each cardinality constraint symbol is composed of two parts

Maximum cardinality—the part of the cardinality constraint symbol closer to the entity rectangle; • Minimum cardinality (participation)—the part of the cardinality constraint symbol farther away from the entity rectangle

"ReportsTo" relationship

One direction: rectangle (an employee) - diamond (reports to) - cardinality constraint (exactly one) - rectangle (department); • Opposite direction: rectangle (a department) - diamond (has reporting to it) - cardinality constraint (between zero and many) - rectangle (employees).

Information can be used for: -Operational purposes only -Analytical purposes only -Operational and analytical purposes -Neither for operational nor for analytical purposes -No purpose other than immediate purpose

Operational and analytical purposes

Minimum cardinality can be: One or many Zero or many Optional or mandatory Optional or many Only many

Optional or mandatory

Which of the following is NOT a possible cardinality constraint? Optional one Optional zero Optional many Mandatory one Mandatory many

Optional zero

How are databases designed?

System analysts or database designers follow three steps to plan a database 1. Create an organizational structure for the data. 2. Design an interface that makes the database user- friendly. 3. Set up reporting capabilities to allow for inquiry and response.

A regular entity must have at least one of the following attributes: Unique attribute Composite attribute Multivalued attribute Derived attribute Optional attribute

Unique attribute

According to the ERD below, this vet office wants to ensure high quality care. We can see that they only allow a pet to see how many different vets at this office? A pet can be seen by a maximum of 2 or more different vets A pet can be seen by a minimum of 10 or a maximum of 50 different vets A pet can be seen by a minimum of 1 or a maximum of 2 different vets A pet can be seen by 1 or more different vets

a pet can be seen by a minimum of 1 or maximum of 2 different vets

composite primary key

a primary key that is composed of multiple attributes

Composite primary key

a primary key that is composed of multiple columns •Column names of a composite primary key are underlined, because combined together they form the primary key

ternary relationship

a relationship that exists when three entities are associated

Relationship roles

additional syntax that can be used in ER diagrams at the discretion of a data modeler to clarify the role of each entity in a relationship

relationship roles

additional syntax that can be used in ER diagrams at the discretion of a data modeler to clarify the role of each entity in a relationship

Which of the following is NOT a type of a functional dependency: partial full-key transitive none of these all are functional dependencies

all are functional dependency

Composite attribute

attribute that is composed of several attributes •Not an additional attribute of an entity •Its purpose is to indicate a situation in which a collection of attributes has an additional meaning, besides the individual meanings of each attribute

Composite unique attribute

attribute that is composed of several attributes and whose value is different for each entity instance purpose is to indicate a situation is which a collection of attributes has an additional meaning, besides the individual meanings of each attribute

Unique Attribute

attribute whose value is different for each entity instance Every regular entity must have at least one unique attribute

unique attribute

attribute whose value is different for each entity instance. Every regular entity must have at least one unique attribute

Derived attribute

attribute whose values are calculated and not permanently stored in a database

Foreign Key

attributes - primary key from the original table 1. duplicates are allowed and actually necessary

Analytical database

collects and presents analytical information in support of analytical tasks

Operational database

collects and presents operational information in support of daily operational procedures and processes

Primary key

column (or a set of columns) whose value is unique for each row •Each relation must have a primary key •The name of the primary key column is underlined in order to distinguish it from the other columns in the relation

The following attribute represents a collection of attributes: Uniquie attribute Composite attribute Multivalued attribute Derived attribute Optional attribute

composite attribute

metadata

data that describes the structure and the properties of the data - Metadata is essential for the proper understanding and use of the data

Each field in an Access table must be assigned a __________. value information data type data

data type

currency

dollar amounts such as a bank account balance

multivalued attribute

double framed oval

Autonumber data type option

enables automatic generation of consecutive numeric data values in a column

Direct interaction

end-user communicating with the database directly through DBMS

Indirect interaction

end-user communicating with the database through front-end applications

owner entity

entity whose unique attribute provides a mechanism for identifying instance of a weak entity

Owner entity

entity whose unique attribute provides a mechanism for identifying instances of a weak entity

Cardinality

expresses the specific number of instances in an entity 1. one-to-many 2. one-to-one 3. many-to-many

What is the smallest element of a database? - text - file -record - field

field

numberic

numbers such as a count of inventoried products

Front-end application analysts

in charge of collecting and defining requirements for front-end applications

Front-end applications developers

in charge of creating the front-end applications

Database developers

in charge of implementing the database model as a functioning database using the DBMS software

Relation - In order for a table to be a relation in the following conditions must hold:

oWithin one table, each column must have a unique name. oWithin one table, each row must be unique. oAll values in each column must be from the same (predefined) domain. oWithin each row, each value in each column must be single valued (one value from a predefined domain, within each row in each column). •Two additional properties of each table: oOrder of columns is irrelevant. oOrder of rows is irrelevant.

relationship instances

occur when an instance of one entity is related to an instance of another entity via a relationship.

Relationship instances

occurrences of a relationship •Occur when an instance of one entity is related to an instance of another entity via a relationship •Relationship themselves are depicted in the ER diagrams while relationship instances are not •Relationship instances are eventually recorded in the database that is created based on the ER diagram

Entity instances (entity members)

occurrences of an entity •Entities themselves are depicted in the ER diagrams while entity instances are not •Entity instances are eventually recorded in the database that is created based on the ER diagram

Unary relationship (recursive relationship)

occurs when an entity is involved in a relationship with itself (degree 1 relationship)

record locking

occurs when users attempt to edit existing records on a multi-user system allows only one user at a time to edit or delete a record

According to the ERD below, a product can belong to _________ brand(s). none of these answers one to many one and only one zero to one

one and only one

Maximum Cardinality can be: Zero or many Optional or mandatory Optional or many Only optional One or many

one or many

Database administrators (DBAs)

perform the tasks related to the maintenance and administration of a database system

Database administration and maintenance

performing activities that support the database end user, including dealing with technical issues, such as: •Providing security for the information contained in the database •Ensuring sufficient hard-drive space for the database content Implementing the backup and recovery procedures

Which of the following terms is defined as: duplication of data in several fields; an enemy of data integrity - validation -normalization - redundancy - integrity

redundancy

Degree of a relationship

reflects how many entities are involved in a relationship

Degree of a relationship

reflects how many entities are involved in the relationship

column

relation is sometimes referred to as a field or attribute (of a relation).

entity integrity constraint

relational database rule that states all primary key columns must have values.

Identifying relationship

relationship between a weak entity and its owner entity in which each instance of a weak entity is associated with exactly one instance of an owner entity •Each weak entity must be associated with its owner entity via an identifying relationship •Unique attribute from the owner entity uniquely identifies every instance of the weak entity via an identifying relationship

identifying relationship

relationship between a weak entity and its owner entity in which each instance of a weak entity is associated with exactly one instance of an owner entity •Each weak entity must be associated with its owner entity via an identifying relationship •Unique attribute from the owner entity uniquely identifies every instance of the weak entity via an identifying relationship

binary relationship

relationship between two entities

Binary relationship

relationship between two entities (degree 2 relationship)

The first and most critical step in the development of a database is:

requirements, collection, definition, and visualization If this step is successful, the remaining steps have a great chance of success.

Relational database constraints

rules that a relational database has to satisfy in order to be valid •Implicit constraints o The implicit relational database model rules that a relational database must satisfy in order to be valid •User-defined constraints o Database constraints that are added by the database designer

multiple relationships between same entities

same entities in an ER diagram can be related via more than one relationship

Hybrid Databases

several different models combined to allow more effective data handling

Relational schema

visual depiction of the relational database model

Weak entity example

weak entity is marked by double framed rectangle, an identifying relationship is identified by double framed diamond

Why do we need databases?

•Without a database you would have to keep records on paper which causes the chance for many more errors. Examples include: •Card Catalog System in the Library •Video Rental Service •Student data in a college •Social Security Information •Tax Information...and many more uses

maximum cardinality symbol (many)

may have to have many relationships

FirstName is an example of: -Data -Metadata -Data Type -DBMS -All of the above

metadata

many in data base speak

"any number of" including zero

Which of the following is a legitimate exact minimum and maximum cardinality? (20,10) (5,0) (5,10) (5,4) (0,0)

(5,10)

Database designers

(a.k.a. database modelers or architects) - involved in the database modeling stage

Partial key

- attribute of a weak entity that combined with the unique attribute of the owner entity uniquely identifies the weak entity's instances •Combination of the partial key and the unique attribute from the owner entity uniquely identifies every instance of the weak entity

partial key

- attribute of a weak entity that combined with the unique attribute of the owner entity uniquely identifies the weak entity's instances •Combination of the partial key and the unique attribute from the owner entity uniquely identifies every instance of the weak entity

Database system

- computer-based system whose purpose is to enable an efficient interaction between the users and the information captured in a database

Database modeling (logical database modeling )

- creation of the database model that is implementable by the DBMS software Logical database modeling follows conceptual database modeling

Entity integrity constraint

- in a relational table, no primary key column can have null (empty) values •A rule stating that no primary key column can be optional •Every RDBMS enforces this rule

Database analysts

- involved in the requirements collection, definition, and visualization stage

Front-end applications

- provide a mechanism for easy interaction between the users and the DBMS

Database

- structured collection of related data stored on a computer medium •Organizes the data in a way that facilitates efficient access to the information captured in the data

End-users (business-users)

- users using a database system to support their tasks and processes

Database implementation

- using a DBMS to implement the database model as an actual database •Most modern databases are implemented using a relational DBMS (RDBMS) software •SQL (Structured Query Language) is a language used by most RDBMS packages

Referential integrity constraint

-Referential integrity constraint - In each row of a relation containing a foreign key, the value of the foreign key EITHER matches one of the values in the primary key column of the referred relation OR the value of the foreign key is null (empty). •A rule that defines values that are valid for use in foreign keys •In a relational schema lines pointing from the foreign key to the corresponding primary key are referred to as referential integrity constraint lines

Data Integrity

-data integrity - the accuracy of information provided to database users -redundancy - duplication of data in several fields; an enemy of data integrity -normalization - prevents duplicate data storage and reduces the chance that some data will not be updated when changes are made

Flat File Databases: does NOT have the ability to interrelate data in an organized structure.

-database contains only one table or file -simple and easy to use -slower to respond because records must be searched sequentially -generally consume more disk space

Office Information Systems

-designed as replacement for paper-based information systems -gives quick access to office paperwork -Most banks, credit cards, student loan companies, 401K plans, and other entities now offer online statement delivery -Helps to produce a paperless setup.

Decision Support Systems

-designed to help management make decisions about an operation -allows managers to work with "what if" scenarios -Lets businesses know if certain decisions will lead to an overall success or fail

Developing front-end applications

-designing and creating applications for indirect use by the end-users •Front-end applications are based on the database model and the requirements specifying the front-end functionalities •Front-end applications contain interfaces (such as forms and reports) accessible via a navigation mechanism (such as a menu)

Data

-facts that are recorded and can be accessed •Data formats - text, numbers, figures, graphics, images, audio/video recordings and more •Data is recorded and kept because it is considered to be of use to an intended user

Operational Databases

-operational database tracks an operation or situation, such as the inventory of a store -distributed database - is spread across multiple networked computers- each computer shares a portion of the data- can hold more information- is cheaper- is easier to use -Examples: E-Commerce Stores, Retailers with bar codes..once an item is ordered or scanned, the item is deducted from inventory

Factory Automation Systems

-runs an assembly line directly, controlling manufacturing processes from shop-floor level of conveyor belts and robots -may be part of complete CIM system that controls manufacturing process from beginning to end

Database management system (DBMS)

-software used for: •Creation of databases •Insertion, storage, retrieval, update, and deletion of the data in the database •Maintenance of databases - All interaction occurs via the DBMS

Object-Oriented Databases

-stores data in the form of objects (units of object-oriented programming logic) -object contains data related to the object and the actions to be performed on the object -speeds up database functions and makes database development easier -Example: payroll data that not only has information but also calculations for tax and benefit withholding

Data Warehouses

-used to store data gathered from one or more databases does not change, delete, or manipulate data it stores like an operational database

How many entities are involved in a unary relationship? 1 2 More than 2 Between 1 and 2 Less than 1

1

What is a database and how does it organize information?

A database is a computerized system for storing information in an organized manner so that it can be searched for and retrieved when needed. Information is stored in the form of fields, records, and files

Which of the following is the most correct definition of a database? - A database is a system for storing information in an organized manner so that it can be searched for and retrieved when needed. Information is stored in the form of fields, and files. - A database is a computerized system for storing information in an organized manner so that it can be searched for and retrieved when needed. Information is stored in the form of fields, records, and files. - A database is a computerized system for storing information in an unorganized manner so that it can be searched for and retrieved when needed. Information is stored in the form of fields, records, and files. - A database is a non-computerized system for storing information in an organized manner so that it can be searched for and retrieved when needed. Information is stored in the form of fields, records, and files.

A database is a computerized system for storing information in an organized manner so that it can be searched for and retrieved when needed. Information is stored in the form of fields, records, and files.

Which of the following statements would be used to drop a column from the table? CREATE TABLE ALTER TABLE DROP TABLE DROP COLUMN

ALTER Table

The Navigation Pane displays _____________________ . reports All Access Objects tables queries

All Access Objects

Which of the following is a format in which data can appear? -Text -Numbers -Image -All of the above -None of the above

All of the Above

End Users can vary in: -Their level of technical sophistication -The amount of data they need -The frequency with which they access the database system -All of the above -None of the above

All of the above

Which of the following is a component of a database system? -Database -DBMS -Front-end applications -All of the above -None of the above

All of the above

Which of the following is a database use action? -Inserting data -Deleting data -Modifying data -Retrieving data -All of the above

All of the above

What are the types of data processing?

Batch processing, transactional process, mixed forms of processing (transactional and batch processing in the same system

Per the VET table, why did the creator go from one to two tables? Cannot describe animals fully in one table DB creator wanted to cause more work for data entry operators Because the table had too many attributes Because the table in original form was describing two entities None of these answers

Because the table in original form was describing two entities

________ are in charge of implementing the database model as a functioning database using the DBMS software. -Database analysts -Database architects -Database developers -DBAs -Database end users

Database developers

DATABASE SCOPE

Databases can vary in their scope from small single-user (personal) databases to large enterprise databases that can be used by thousands of end-users §Regardless of their scope, all databases go through the same fundamental development steps (requirements, modeling, implementation, deployment, use, etc.)

What is reversing the effect of normalization by joining normalized relations into a relation that is not normalized, to improve query performance Delete anomaly Denormalization Retrofitting Reorganization Any of these answers

Denormalization

According to the ERD below, which type of attribute is Age under Pet? Derived optional composite calculated

Derived

The values of the following attribute are NOT permanently stored in a database. Unique attribute Composite attribute Multivalued attribute Derived attribute Optional attribute

Derived attribute

Which of the following requires that the end user knows how to issue commands to the specific DBMS? -Direct interaction -Indirect interaction -Both direct and indirect interaction -Neither direct nor indirect interaction -Sometimes direct and sometimes indirect interaction (but not both at the same time)

Direct Interaction

weak entity

ER diagram construct depicting an entity that does not have a unique attribute of it's own

Weak entity

ER diagram construct depicting an entity that does not have a unique attribute of its own

DMBS is a presentation software (such as MS Power Point). (T/F)

False

During a proper requirements collection process, the requirements can be added iteratively and implicitly. (T/F)

False

Every normalized database is eventually denormalized. (T/F)

False

Full key functional dependency occurs when a candidate key functionally determines the column of a relation and no separate component (T/F)

False

Once a field name is established you cannot rename it in Access (T/F)

False

Traditional database organizes data in a hierarchy. Which of the following is the correct hierarchy? - Database, Field, Record, Data File - Field, Record, Data File, Database - Record, Data File, Field, Database - Record, Field, Data File, Database

Field, Record, data file, database

Foreign Key

Foreign key - column in a relation that refers to a primary key column in another (referred) relation •A mechanism that is used to depict relationships in the relational database model •For every occurrence of a foreign key, the relational schema contains a line pointing from the foreign key to the corresponding primary key

referential integrity constraint

In each row of a relation containing a foreign key, the value of the foreign key EITHER matches one of the values in the primary key column of the referred relation OR the value of the foreign key is null (empty).

TERNARY (AND HIGHER DEGREE) RELATIONSHIPS

In practice, ternary relationships are relatively rare, and relationships of degree higher than 3 are rarer still

what is an outer query? A query that contains within itself another query (nested query) is called an ADDITIONAL query. A query may NOT contain another query (or queries). In MOST cases, a query may contain another query (or queries). A query that contains within itself another query (nested query) is called an outer query. In some cases, a query may contain another query (or queries). A query that contains within itself another query (nested query) is called an outer query.

In some cases, a query may contain another query (or queries). A query that contains within itself another query (nested query) is called an outer query.

The description of the structure and the properties of the data best defines which category? -Information -Data -Metadata -DBMS -Front-End Application

Metadata

relational DBMS (RDBMS)

Most contemporary commercial DBMS software packages, are relational DBMS (RDBMS) software packages

The following attribute can contain more than one value for each entity instance. Unique attribute Composite attribute Multivalued attribute Derived attribute Optional attribute

Multivalued attribute

_______________ is a column in a relation that is neither a primary nor a candidate key column Composite Null Unique Nonkey

Nonkey

optional participation symbol

Not required relationship?

MAPPING ER DIAGRAMS INTO RELATIONAL SCHEMAS

Once an ER diagram is constructed, it is subsequently mapped into a relational schema (collection of relations)

STEPS IN THE DEVELOPMENT OF DATABASE SYSTEMS

Requirements collection, definition, and visualization - results in the requirements specifying which data the future database system will hold and in what fashion, and what the capabilities and functionalities of the database system will be •The collected requirements should be clearly defined and stated in a written document, and then visualized Requirements collection, definition, and visualization •Conceptual database model - a visualization of requirements by using a conceptual data modeling technique (such as entity-relationship [ER] modeling)

three types of relationships

Three types of relationships (maximum cardinality-wise)

Planning and Designing Database Systems

User requests and enters data through the front-end interface program. The database management system manages the retrieval and update of the database itself.

standard Chen ER notation

We have chosen this notation for the following reasons: • Pedagogical value: easy for novices to learn and use; • Completeness: All the basic ER concepts are represented. • Clarity and visibility: All the concepts are graphically represented and every concept is easily distinguished. • Compatibility with software: This book provides access to the database modeling software tool ERDPlus (available at erdplus.com), which uses the same ER notation

relationships

Within an ER diagram, each entity must be related to at least one other entity via a construct called a relationship. Relationships3 are shown in an ER diagram as a diamond, with a word or phrase naming the relationship inside the diamond. The diamond is connected with lines to the entities that take part in the relationship.

data directory

a body of metadata

row

a row in a relation is sometimes referred to as a tuple or record

Entity-relationship (ER) modeling

conceptual database modeling technique •Enables the structuring and organizing of the requirements collection process •Provides a way to graphically represent the requirements

hyperlink

connects a record to a web address

Associative entity

construct used as an alternative way of depicting M:N relationships •Associative entities do not have unique or partially unique attributes, and often do not have any attributes at all

Associative entity

construct used as an alternative way of depicting M:N relationships •Associative entities do not have unique or partially unique attributes, and often do not have any attributes at all For relationships with a degree higher than 2 such as ternary relationships, associative entities provide a way to eliminate potential ambiguities in the ER diagrams

Entities

constructs that represent what the database keeps track of Basic building blocks of ER diagram represent real-world notations, such as people, places, objects, events, items, and other concepts

Entities

constructs that represent what the database keeps track of •The basic building blocks of an ER diagram •Represent various real world notions, such as people, places, objects, events, items, and other concepts •Within one ERD each entity must have a different name

List the SQL aggregate functions COUNT, SUM, AVG, MIN, and MAX COUNT, SAM, AVG, MIN, and MAX clustered COUNT, SUM, AVG, COS, and MAX

count, sum, avg, min, and max

Cardinality constraints

depict how many instances of one entity can be associated with instances of another entity •Maximum cardinality oOne (represented by a straight bar: I) oMany (represented by a crow's foot symbol) •Minimum cardinality (participation) oOptional (represented by a circular symbol: 0) Mandatory (represented by a straight bar: I)

Attribute

depiction of a characteristic of an entity •Represents the details that will be recorded for each entity instance •Within one entity, each attribute must have a different name

attribute (of an entity)

describes a characteristic of an entity, attributes represent the details that will be recorded for each entity instance

sequence (or autonumber)

integer that automatically increases whenever used: generally is used to make a primary key that automatically changes in order to be unique

SQL (Structured Query Language)

language used by most relational DBMS software packages Among its features, SQL includes commands for creating, modifying, and deleting database structures. These commands are used during database implementation.

memo

lengthy text information, notes, or history

Database response time

log time between a user issuing a command and the database system taking action

Relational database model

logical database model that represents a database as a collection of related tables

Ternary relationship

relationship involving three entities (degree 3 relationship)

Database deployment

releasing the database system for use by the end users

ternary relationship - A many to many to one ternary relationship

replaced with binary relationship

A ___________ is a formatted printout (or screen display) of the contents of one or more tables or queries record query report table

report

______________ offer a more visually appealing format for the data queries reports records forms

reports

Database metadata

represents the structure of the database •Database content that is not the data itself (data about the data) •Contains: -Names of data structures -Data types -Data descriptions -Other information describing the characteristics of the data

Relation

table in a relational database •A table containing rows and columns •The main construct in the relational database model •Every relation is a table, not every table is a relation

alphanumeric

textual information such as a person's name

Analytical information

the information collected and used in support of analytical tasks •Analytical information is based on operational (transactional) information

Operational information (transactional information)

the information collected and used in support of day to day operational needs in businesses and other organizations

Requesting information involves what?

the use of a query language such as Structured Query Language (SQL)

Database end users

use a database system to support their work- or life-related tasks and processes •Users differ in: Level of technical sophistication Amount of data that they need Frequency with which they access the database system

Which of the following is a logical field type? - yes/no -currency - alphanumeric - date

yes/no

logical

yes/no states such as "married" or "retired"

According to the ERD below, every product has _______ promotion(s). One to many one and only one zero to many zero or one

zero to many

DATABASE REQUIREMENTS AND ER MODEL USAGE

§ER modeling provides a straightforward technique for collecting, structuring, and visualizing requirements §An understanding of ER modeling is crucial, not just for creating ER models based on the requirements, but also during the requirements collection process itself §It helps keep the focus on asking or seeking answers to the right questions in order to establish the relevant facts about entities, attributes, and relationships §One of the common mistakes that beginners make when engaging in ER modeling for the first time is not recognizing the difference between an entity and the ER diagram itself

M:N RELATIONSHIPS WITH MULTIPLE INSTANCES BETWEEN THE SAME ENTITIES

§In some cases, M:N relationships can have multiple occurrences between the same instances of involved entities •The following examples illustrates such cases

Mapping 1:1 relationships

•1:1 relationships are mapped in the same way as 1:M relationships •One of the resulting relations will have a foreign key pointing to the primary key of another resulting relation •One of the mapped relations is chosen to have a foreign key referring to the primary key of the other mapped relation o In cases when there is no particular advantage in choosing which resulting relation will include a foreign key, the choice can be arbitrary o In other cases one choice can be more efficient than the other

User-defined constraints

•Added by the database designers

Mapping entities with multivalued attributes into relational database constructs

•An entity containing the multivalued attribute is mapped without the multi-valued attribute •The multi-valued attribute is mapped as a separate relation that has a column representing the multivalued attribute and a foreign key column referring to the primary key of the relation resulting from the entity itself oBoth of these columns form a composite primary key for the separate relation

Mapping associative entities

•Associative entities are mapped into relational database constructs in the identical way as M:N relationships

Examples of Database Software

•Business/Enterprise: •Top DBMS is Oracle •End PC User: •Top DBMS is Access

Mapping derived attributes

•Derived attributes are not mapped as a part of the relational schema •They are implemented as a part of the database front-end application

Granularity of the table

•Describes what is depicted by one row in the table

Mapping entities with composite attributes into relations

•Each component of a composite attribute is mapped as a column of a relation •The composite attribute itself does not appear in the mapped relation •An entity whose only unique attribute is a composite attribute is mapped as a relation with a composite primary key •Optional attribute of an entity is mapped as an optional column

Mapping entities into relations

•Each regular entity becomes a relation •Each regular attribute of a regular entity becomes a column of the newly created relation •If an entity has a single unique attribute, then that attribute becomes the primary key in the resulting mapped relation

Implicit constraints

•Each relation in a relational schema must have a different name •Each relation must satisfy the following conditions: oEach column must have a different name oEach row must be unique oDomain constraint - all values in each column must be from the same predefined domain oIn each row, each value in each column must be single valued oThe order of columns is irrelevant oThe order of rows is irrelevant •Primary key constraint - each relation must have a primary key, which is a column (or a set of columns) whose value is unique for each row •Entity integrity constraint •Referential integrity constraint

Mapping multiple relationships between the same entities

•Each relationship is mapped

Mapping M:N unary relationships

•In addition to the relation representing the entity involved in a unary M:N relationship, another relation is created to represent the M:N relationship itself •This new relation has two foreign keys, both of them corresponding to the primary key of the relation representing the entity involved in the unary M:N relationship •Each of the foreign keys is used as a part of the composite primary key of the new relation

Mapping M:N relationships

•In addition to the two relations representing the two entities involved in the M:N relationship, another relation is created to represent the M:N relationship itself •This new relation has two foreign keys, corresponding to the primary keys of the two relations representing the two entities involved in the M:N relationship •The two foreign keys form the composite primary key of the new relation

Identifying relationship is either 1:M or 1:1 relationship

•In case of 1:M identifying relationship, a weak entity must have a partial key attribute •In case of 1:1 identifying relationship, a weak entity doesn't need to have a partial key attribute

Weak entity -Identifying relationship is either 1:M or 1:1 relationship

•In case of 1:M identifying relationship, a weak entity must have a partial key attribute •In case of 1:1 identifying relationship, a weak entity doesn't need to have a partial key attribute

Relationship attributes

•In some cases M:N relationships can actually have attributes of their own

Exact minimum and maximum cardinality in relationships

•In some cases the exact minimum and/or maximum cardinality in relationships is known in advance •Exact minimum/and or maximum cardinalities can be depicted in ER diagrams

Mapping 1:1 unary relationships

•Mapped in the same way as 1:M unary relationships

Mapping entities with candidate keys (multiple unique attributes) into relations

•One of the candidate keys is chosen by the database designer as the primary key during the mapping process •Other candidate keys are mapped as non-primary key columns

Types of Relationships (maximum cardinality-wise

•One-to-one relationship (1:1) •One-to-many relationship (1:M) Many-to-many relationship (M:N)

Multiple relationships between same entities

•Same entities in an ER diagram can be related via more than one relationship

Mapping ternary relationships

•Ternary relationships are used as many-to-many-to-many relationships •A new relation is created with foreign keys from the participating entities forming a composite primary key of the new relation

Mapping 1:M unary relationships

•The relation mapped from an entity involved in a 1:M unary relationship contains a foreign key that corresponds to its own primary key

Mapping 1:M relationships - Mandatory participation on both sides

•The relation mapped from the entity on the M side of the 1:M relationship has a foreign key that corresponds to the primary key of the relation mapped from the 1 side of the 1:M relationship. Mandatory participation on both sides

Mapping unary relationships

•Unary relationships in ER diagrams are mapped in the same way as binary relationships