Exam 1 (ITSS 4301)

Theme Characteristic

- A __________ is some identifiable thing that users want to track - Keeps data well organized - Easy to modify the data - Easy to modify the database structure - Avoids redundant data

Systems Development

- A logical sequence of activities used to: * identify new systems needs * develop new systems to support those needs - A model for reducing risk through planning, execution, control, and documentation

2NF (SecondNormalForm)

- In this form, each table is in 1NF and all non-key attributes are determined determined by only the entire PK -Candidate keys are determined through the process of determinant / functional dependency identification * All attributes are functionally dependent on one of the candidate

Types of Binary Relationships

- One to One (1:1) Relationship - One to Many (1:N) Relationship - Many to Many (N:M) Relationship

Databases vs Excel

A ____(1)____ provides much of the same functionality as a _______(2)______, but is designed to handle certain data situations beyond what a spreadsheet can do answer (1) characteristics: - ...are optimized to scan large amounts of data • Millions of rows of data are easy to handle - ...are designed to handle many (some support thousands) of users at a time - ...have built-in data integrity • E.g. Disallow adding a flight segment if the airport city doesn't exist; can log changes made - ...have sophisticated, granular secure access mechanisms

Key

A __________ is one or more columns of a relation that is used to identify a row -Can be unique (PK) -Can be non-unique (FK)

user

A __________ of a database system will: - Use a database application to track things - Use forms to enter, read, delete, and query data - Produce reports

(Problem with a) Null Value

A ____________ is often ambiguous. It could mean: - the column value is not appropriate for the specific row - the column value is not decided - the column value is unknown

foreign key

A _____________ key is just a field that has been identified as able to function as a connection point for the primary

database application

A _______________ is a set of one or more computer programs that serves as an intermediary between the user and the DBMS. These read or modify database data by sending SQL statements to the DBMS They also present data to the user in the format of forms and reports

False (that would be an application)

A database is a set of one or more computer programs that serves as an intermediary between the users and the database management system (DBMS). a) True b) False

False

A key must be unique. a) True b) False

a self-describing collection of related tables

A relational database is ________. a) a library of queries and data files for querying b) a set of metadata c) a self-describing collection of related tables d) a set of applications and the data sets for those applications e) a collection of forms and reports that support a given purp

ID dependent (weak entity)

A weak entity who's identifier is always a composite and includes the identifier of another (strong) entity - the first part of the identifier is the identifier for the strong entity - the second part of the identifier is the identifier for the weak entity itself

Entity

A(n) _________ is a 2-dimensional array that represents something of importance to a user • It represents one theme or topic - Can be a person, place, object, event or concept • Examples: - Person: EMPLOYEE, STUDENT, PATIENT - Place: STATE, REGION, COUNTRY - Object: MACHINE, BUILDING - Event: SALE, REGISTRATION - Concept: ACCOUNT, COURSE

Information Engineering (IE)

[James Martin 1990] It uses "crow's feet" to show the many sides of a relationship, and is sometimes called the crow's foot model. Type of ERD

1NF (First Normal Form)

In this form: -each cell has only one value -all entries in a column are of the same kind -Each column can hold one value. -Each column is of only one kind/domain (e.g. LastName)

Relational Databases

__________ data exists in multiple tables; Focus is on connecting uniquely "themed" tables physically store data in separate table Once you relate separate tables, you've created a relational structure

Big Data

__________ is the current term for the enormous datasets generated by Web and mobile applications. • ___________ datasets are often stored in non- relational databases such as NoSQL databases. • ____________ will be studied in more detail, later in the course. -Think Moore's Law in relation to data

SQL (Structured Query Language)

___________ is an international standard for creating, processing, and querying databases and their tables. • Many databases use this to retrieve, format, report, insert, delete, and/or modify data for users.

Zero anomalies and redundancies

Take these steps to achieve this: 1. Rows contain data about an entity 2. Columns contain data about attributes of the entity 3. Cells of the table hold a single value 4. All entries in a column are of the same kind 5. Each column has a unique name 6. The order of the columns is unimportant 7. The order of the rows is unimportant 8. No two rows may hold identical sets of data values

Cloud Computing

____________ is the use of another company's hardware to conduct business. This requires access via an Internet connection

Primary keys

____________ keys ensure all records in a table are unique - Student ID is a good example

DBMS

____________ performs the following functions/roles -Creates DB -creates tables -Creates supporting structures (indexes) -Reads db data -Modify db data (insert, update, delete)

DBMS (Database Management System)

_____________ serves as an intermediary between database applications and the database. • manages and controls database activities. • creates, processes, and administers the databases it controls.

Referential Integrity (Constraints)

________________(s) ensure that the values of a column in one table are valid based on the values in another table. - For example, if a 5 was entered as a CustomerID in the PROJECT table, a customer having a CustomerID value of 5 must exist in the CUSTOMER table.

True

The DBMS is used to create the database itself (T/F)

Surrogate Key (example)

ex: note 'u' = underlined composite a. Start with this: PROPERTY ('u'Street, 'u'City, 'u'State, 'u'Zip, OwnerID b. Turns into PROPERTY ('u'PropertyID, Street, City, State, Zip, OwnerID) What kind of key was added here?

primary key

is a candidate key that is chosen as the key the DBMS will use to uniquely identify each row in a relation - an attribute (or composite of attributes) that determines all the other attributes

database

is a collection of data that is organized so that it can easily be accessed, managed, and updated

Workbook

is a collection of worksheets the same way that a database is a collection of tables

surrogate key

is a column with a unique, DBMS-assigned identifier that has been added to a table to be the primary key. -Used when no good "natural key exists for the entity -The ideal _______________ is short and numeric and never changes - ____________ will have no inherent meaning to users, thus they are often hidden in forms, query results, and reports. ex: note 'u' = underlined composite a. PROPERTY ('u'Street, 'u'City, 'u'State, 'u'Zip, OwnerID Turns into b. PROPERTY ('u'PropertyID, Street, City, State, Zip, OwnerID)

Candidate Key

is a determinant that will possibly functionally determine all other attributes in the row • An attribute (or composite of attributes) that determines all the other attributes • Each attribute must further define the entity • One candidate key is selected as the primary key

Candidate key

is a key that uniquely identify each row in a relation -A relation can have multiple ____________s A middle stage in determining the PK

Unified Modeling Language (UML)

is a set of structures and techniques for modeling and designing object-oriented programs (OOP) and applications

Query

is a structured statement used to extract data from a database -can locate information stored ACROSS MULTIPLE TABLES

IDEFIX (Integrated Definition 1, Extended)

is a version of the E-R model that is a national standard.

Web database application

is an application with a Web user interface that is dependent upon a database to store the data needed by the application. • Today's Internet and mobile device-world relies on the user having a Web browser or a mobile app to access an application powered by data in a database.

identifier

is an attribute that names or identifies instances such as: - SocialSecurityNumber - StudentID - EmployeeID Think of them as a good guess of the PK We need _________ to be able to explain other relationship concepts -think first phase of determining the Primary Key

entity instance

is an object built from the entity class. In other words, an entity class is the occurrence of a particular entity -Single and Unique sub-theme

Maximum cardinality

is the maximum number of entity instances that may participate in a relationship instance—one, many, or some other fixed number. Base word means "Count"

Minimum cardinality

is the minimum number of entity instances that must participate in a relationship instance. These values typically assume a value of zero (optional) or one (mandatory).

Normalization (High Level Def)

is the process of (or set of steps for) breaking an entity with more than one theme into a set of relations such that each has only one theme ("unbundle any overlapping entities") -Along the way of the process, determinants / functional dependencies and keys are established

SDLC (Systems development life cycle)

the classic methodology used in systems analysis and design to develop information systems, including the database portion.

SDLC (approaches)

The following are approaches to what: 1. Waterfall 2. Agile

Organizational DBMS Products

The following are examples of what kinds of products: - Microsoft's SQL Server 2016 - Oracle Database XE - Sun Microsystem's MySQL 5.7 - IBM DB2 - Sybase ASE

SDLC Stages

The following are what: -Systems Definition -Requirement Analysis -Component Design -Implementation -System Maintenance

Database Components

The following are: - Users - Database Application - Database Management System (DBMS) - Database

Database Development

The following processes describe what: 1. Planning 2. Analysis 3. Logical Design 4. Physical Database Design 5. Implementation

Normalization Process

The goal/steps of this process is to: 1. Identify all the candidate keys of the relation. 2. Identify all the determinants/functional dependencies in the relation. 3. Examine the determinants of the functional dependencies. If any determinant is not a candidate key, the relation is not well formed. In this case: a. Place the columns of the in a new relation of their own. b. Make the determinant the primary key of the new relation. c. Leave a copy of the determinant as a foreign key in the original relation. d. Create a referential integrity constraint between the original and the new relation. 4. Repeat Step 3 until every determinant of every relation is a candidate key.

DBMS

The key role of this is to organize the data through the metadata structure. You cant load any data until a metadata structure in the firs place

Degree

The number of entity classes in the relationships is known as ____________.

True

The primary key is used both to identify unique rows in a relation and to represent rows in relationships. a) True b) False

Database (expanded)

The purpose of ________ is to keep track of things (e.g. airline reservations) - Unlike a spreadsheet or list, a ________ may store information that is more complicated than a simple listing (e.g. airline reservations) - It does this by storing not just the data, but also relationships (which we'll cover later) - Allows business questions (queries) to be answered - Created/managed by specialized database software - ...are optimized to scan large amounts of data • Millions of rows of data are easy to handle - ...are designed to handle many (some support thousands) of users at a time - ...have built-in data integrity • E.g. Disallow adding a flight segment if the airport city doesn't exist; can log changes made - ...have sophisticated, granular secure access mechanisms

Database Elements

These are: -User data -Metadata -Indexes and other overhead data -Application metadata

Personal database systems

These kinds of database systems have these characteristics: - Have one application - Have only a few tables - Each table would have only a few hundred rows of data - Involve only one computer - Support one user at a time

business intelligence systems (BI)

These systems consists of tools used to analyze and report on company data.

BCNF (Boyce Codd Normal Form)

This form has the following characteristics: - each table is in 3NF and all determinants are candidate keys -All remaining determinants of that entity are candidate keys (i.e. could be the PK)

Functional Dependency

This is a logical relationship in which the value of one item in the relationship can be determined by knowing the value of the other item

Systems Development

This is a set of activities used to build an information system -Database development is a part of this - __________ projects can be very complicated. A structured approach is needed

Systems analysis and design

This is the process of creating and maintaining information systems

Flat data

This kind of data resides in a single table

Requirements Analysis (Gathering Stage)

This stage in the SDLC database development process has these components: -User Interviews -Forms -Reports -Queries -Application Programs -Websites -Use Cases -Business Rules e.g. If user needs a report that shows inventory, ERD should include an INVENTORY entity

Uniqueness (identifiers)

This terms is a descriptor of the following: -Identifiers may be unique or non-unique - We're still performing modeling, so we use the term "identifier" to indicate our best guess at the PKs at this point - Only after finishing the normalization process will we know for sure what the real PK is (and that it's unique!)

Composite (identifier)

This type of identifier is one that consists of two or more attributes e.g., EmployeeNumber & LineItemNumber are both required to identify the entity

Enterprise Class (database system)

This type of system: - Supports international organizations - Have hundreds of tables with millions of rows - Have many databases - Supports thousands of concurrent users - In use 24/7 - Includes more than one application

True

To be considered a composite key, a key must contain at least two attributes. a). True b) Fasle

False

Usually, a database table containing both rows and columns is designed to store data for exactly two themes. a) True b) False

True

If the condition exists such that knowing the value of attribute X determines the value of attribute Y, then attribute Y is functionally dependent on attribute X. a) true b) False

Entity Relationship Diagram

- This is a diagram which describes the relationships between entities - It's purpose is to model a given business solution - High-level graphical view - Is not tied to any particular DBMS

associative entity

- This type of entity is used whenever a pure N:M relationship cannot properly hold attributes that are describing aspects of the relationship between two entities. - A new entity is then created to: >> link the two original entities >> hold the attributes

entity class

- is a blueprint, building block or template used to build a specific type of object. -is a collection of fields and associated database operations - An __________ is almost always singular (e.g. CUSTOMER) since it's basically a template -hint: relates to entities

Composite Key

- is a key that contains two or more attributes. • For a key to be a unique identifier, it must often become a this type of key.- -Ex: StudentEmail StudentZipCode

Null Value

- is a missing value in a cell in a relation. • This is different from a zero, space, character, or tab character. • You can eliminate ______________ by requiring an attribute value. • DBMS products allow you to specify whether a ___________ can occur in a column.

Referential Integrity

- states that every value of a foreign key must match a value of an existing primary key. How to maintain this - Must ensure that every value of a foreign key matches a value of the primary key - The DBMS itself can ensure this if referential integrity is turned on between the two relations -Cannot have Nulls in PK or FK

Database Application Functions

-Create and process forms -Process user queries -Create and process reports -Execute applicaiton reports -Execute application logic -Control application

On Prem (or on premise)

-Opposite of cloud computing -the company's own data center resources are used.

Table

-a single-themed bucket where only data that conforms to very specific standards is allowed. -For our purposes, this is called an ENTITY

Relations

-are the evolution of an entity's design -are a 2 dimensional array of rows and columns. - Have specific characteristics • In these, all Anomalies and redundancies have been removed - = it's normalized

3NF (Third Normal Form)

-in this form, each table is in 2NF and no non-key attributes are determined by another non-key attribute -If more than one unique determinant is derived in the 2NF process, the separate theme entities must be separated

Design rules for Normalization

-to be a well-formed relation, every determinant must be a candidate key - any relation that is not well-formed should be broken into two or more relations that are well formed These actions describe the _____________

Database Design Steps

1. Determine entities 2. Determine attributes 3. Convert entities to relations through a process called "normalization" - Remove obvious anomalies / redundancies - Determine functional dependencies - Establish candidate keys - Determine primary key 4. Define relationships to other entities (ERD)

Types of Relationships

1. Unary: 1st degree (recursive) 2. Binary: 2nd degree 3. ternary: 3rd degree

False

An advantage of keeping data in lists, in Excel, is that if you update a data value in one row of data in a list, other occurrences of the same data item in other rows will be automatically updated as well. a) T b) F

weak entity

An entity that cannot exist in the database without the existence of another, related entity -Rounded Corners in ERD

OLAP (online analytical processing)

Data analysis used for research is stored in this type of db. -this is an example of Business Intelligence

Progression to Primary Key

Determinant --> Candidate key --> --->Primary key

True

Ensuring that every value of a foreign key matches a value of the corresponding primary key is an example of a referential integrity constraint. a) True b) False

Attributes

Entities have _________, which describe the entity's characteristics such as: - EmployeeName - DateOfHire - JobSkillCode A(n) __________ has a data type (character, numeric, date, currency, and the like) and properties that are determined from the requirements

Attributes

Entities/Relations have _________, which describe the entity's/relation's characteristics such as: - EmployeeName - DateOfHire - Skill - Student Number • Attributes correspond to column names

determinant

Example: Suppose the cost of cookies is decided by the number of boxes we buy. We would say that the number of boxes would be a ___________ because it dictates the overall cost of the cookies. - the ______________ is always shown on the left hand side of the relationship NumberOfBoxes ---> CookieCost

To the foreign key side of the relationship

In crow's foot notation for a 1:N relationship, which way does the three-prong crow's foot point? a) To the primary key side of the relationship b) To the foreign key side of the relationship c) In the direction of the program flow e) None of the above

Null Value

Missing value in a cell of a relation

Recursive Relationships

When an entity has a relationship with itself

OLTP (online transaction processing database)

When you buy a product online, your purchase becomes a transaction and is recored in the company's _______________ -This type of db maintains current production data for the compnay

the First Three

Which normal forms are the most critical for developing a working database? -The other forms, outside the scope of a database intro course, add refinements that are valuable but not as critical

Each row in the PK entity is related to zero or one row in the FK entity

Which of the following is true of 1:1 relationships? a) Each row in the PK entity can be related to many rows in the FK entity b) Each row in the PK entity is related to zero or one row in the FK entity c) Each relationship in the PK entity can have many related rows in the FK entity and each row d) The relationship is usually not created in a DBMS

Both A and B

Which of the following is true of a N:M relationship? a) Each row in the PK entity can have many related rows in the FK entity and each row in the FK table can have many related rows in the PK entity b) The relationship is usually not created in a DBMS c) Both A and B d) None of the above

Queries against records in the database tables

Which of the following would not be an example of database metadata? a) Properties of tables in a database b) Names of columns in a database and their associated tables c) Queries against records in the database tables d) .Names of tables in a database e) Properties of columns

False

While the relational model for databases appears to hold much promise, few commercial databases have implemented it. a) True b) False