Database Tech 330 Exam 1

Relational Database Management System

- A RDBMS is a DBMS that uses a relational database model. For an example: MySQL Oracle MS SQL Server

Network Database Models (2)

- As implemented, in each named set an OWNER RECORD can have MANY members, but a member can have only ONE owner - Like hierarchical model, sets have parent/child relationships, which allow something called one-to-many relationships, but now many-to-many relationships - Unlike the Hierarchical Model, a record type may participate in multiple different parent/child relations with other record types... so long as in each relation has a different meaning

Advantages of Relational Databases

- Built-in multilevel integrity - logical and physical data independence from database applications - guaranteed data consistency and accuracy - easy data retrieval

SDLC (2)

- Comprised of phases, not necessarily implemented in strict sequential order - Deliverables are producible goals agreed to be accomplished by a specific date. A team is formed which are lead by a project manager to oversee the development process and the PMs are held accountable for the deliveries

Network Database Models

- It also has record types with records or nodes (stored in files) - A relationship between two records is maintained via owner-members or sets. - a set has one record type or node as an owner - a set has one record type or node as a member - a set is named for a purpose - these are still implemented with physical pointed

Relational Database Models - Unlike the other previous models....

- It doesn't use pointers to traverse data relationships - Is very flexible to adapt to changes as it is more logically implemented and therefore has a higher degree of data independence. - Ad hoc (as needed) queries possible. - Understands the notion sets of data, not just single records

Problems with Flat Files and Spreadsheets

- Multipart fields (a field which contains two distinct types of data) - Duplicate fields (separate fields which represent the same type of data) - Multivalued fields (a field which holds more than a single value) - Calculated fields (a field which can be derived or calculated from other fields) - No true primary key (a data row in the file can not be uniquely identified by a simple value)

Hierarchical Database Models

- Oldest database model (after flat files) - Data organized by record types, or nodes, which has instances of records or tuples - Implemented via two pointers in each record... left most child and next sibling

Network Database Model

- uses a mesh-like structure to offer the additional capacity to define many-to-many relationships. - built upon the hierarchical data model. allows each data element to have multiple parent and child records

Weak Entity (2)

--An entity that cannot exist in a database unless another type of entity also exists in that database --Also called ID-dependent weak entity --Have a composite identifier (the identifier for the strong entity and the identifier for the weak entity itself) --All ID-dependent entities are weak entities, but not all weak entities are ID-dependent

Foreign Key (2)

--Used to preserve relationships between entities --Created by placing a primary key from one table into another table

Database Contains

--User data --Metadata --Indexes and other overhead data --Application metadata

Normalization Process

-Identify all candidate keys -Identify all functional dependencies -Examine determinants of the functional dependencies

Objectives Good Design

1. The database supports both required and ad hoc information retrieval 2. The tables are constructed properly and efficiently (Tables have a single subject, distinct fields, uniquely identifiable, and minimal redundancy) 3. Data integrity is imposed at the field, table, and relation levels (it keeps data valid, accurate, and consistent) 4. The database supports business rules relevant to the organization (the data and information retrieved is meaningful) 5. The database lends itself to future growth (it is easy to modify as needs change)

Systems Development Life Cycle (SDLC)

1. planning 2. requirements gathering 3. conceptual design 4. logical design 5. physical design 6. construction 7. implementation and rollout 8. ongoing support

Foreign Key Constraints

A FOREIGN KEY in one table points to a PRIMARY KEY in another table Foreign key [name] references [table name]

DBMS

A ____________ manipulates data in a large collection files and cross references those files. A(n) ________ is a program used to create, process, and administer a database. Ensure that recovery is possible if the database is corrupted

Chen Notation

A data model that describes relationships (1:1, 1:M, and M:N) among entities at the conceptual level with the help of ER diagrams

Primary Key

A field (or group of fields) that uniquely identifies a given entity in a table

Artificial Key

A primary key that is created when no natural key exists. A key for which the possible values have no obvious meaning to the user of the data (example: InvoiceNo, EmployeeID)

One to One

A relationship that have a single corresponding record in a related table - Email, SMS, IM, private message

One to Many

A relationship where one record in a table may have links to many records in another table Web 1.0 Professor belongs to only one department but each department has many professors

Functional Dependencies (2)

A relationship whereby "X" attributes determines the value of "Y" attributes in the same table

Data Integrity: Field-Level

Allows concurrent transactions to access the same row as long as they require the use of different fields (attributes) within that row

Weak Entity

An entity type whose existence depends on some other entity type. Is an entity that depends on another entity for its meaning. For instance, a table of employee dependents which relies on an employee table for it' meaning

Strong Entity

An entity whose data is meaningful without having to reference another entity in the data model.

Data Independence

Applications and data are independent of one another; that is, applications and data are not linked to each other, so all applications are able to access the same data.

Alternative Key

Can be individualized to meet the needs of the user

Column Names

Column, field, attribute

Row

Contains data about an entity

Column

Contains data about attributes of an entity

Data vs Information

Data = raw facts and figures. Information = meaningful interpretation of data.

Data-Driven vs Process-Driven

Data driven: study the data to identify the process Process-driven: study the process to identify the Data

Relational Database Models

Data is organized into familiar 2-dimensional tables, similar to spreadsheets - Relationship between data is implemented via common value fields similar to flat files - These relationships may be stored within or in addition to the primary data tables - This allows not only one-to-one, one-to-many, but also many to many relationships

Normal Form

Database practitioners classify tables into various ________ according to the kinds of problems they have.

Development Environments

Development -> System test -> QA test -> Staging -> Production

Hierarchical Database Model

Enscribe is a hiearchical db. A hierarchical database model is a data model in which the data is organized into a tree-like structure. The structure allows representing information using parent/child relationships: each parent can have many children, but each child has only one parent (also known as a 1-to-many relationship). All attributes of a specific record are listed under an entity type.

Attributes

Fields

First Normal Form

If the data model does not have any repeating fields it is in _____ a. Columns- Each corresponds to a sub object or an attribute of the object represented by entire table. All entities in any column must be the same kind. Example: in the CUSTOMER column only customer names or numbers are permitted --No multi-varied attributes --Find all candidate keys any multivalued attributes (also called repeating groups) have been removed, so there is a single value (possibly null) at the intersection of each row and column of the table. A relation tat has a primary key and in which there are no repeating groups.

Non-key field

In a relational table, data in may change frequently within a...

Boyce-Codd Normal Form

Must be at least 3NF AND have a key as a part of the left side Any remaining anomalies that result from functional dependencies have been removed (because there was more than one possible primary key for the same non-keys). A special type of 3NF when every determinant in the relation is a candidate key

Record

Otherwise known as a tuple, is a collection of attributesor fields which each contain only one data value. - Records are connected to another by a link which is a physical pointer from one record to another - A record may have to may child record links, but only one parent

SDLC: Requirements Gathering

Project Activities: Collect Requirements, Analyze Requirements Database Activities: Collect and analyze user view, identify preliminary entities - Include business processes (flowchart), rules and entities - Account for unknowns - Avoid analysis paralysis - Start working on proposed reports, forms, screens, web pages, etc

SDLC: Construction

Project Activities: Construct Application Software, Build Application Development and Test Environments Database Activities: Create Development and Test Databases, Test any Required Data conversion - Code and test application of modules - Entire applications are assembled - DBA assists in migrating and upgrading DB - Deployment through Development Environment

SDLC: Implementation and Rollout

Project Activities: Create Production Environment, Install Application Components, Train Users, Rollout to Users Database Activities: Create Production Databases, Perform Required Data Conversion - Install new applications into live systems. - Carryout data conversions and migrations onto new systems - Rollout is done in phases or entire system at once

SDLC: Conceptual Design

Project Activities: Design screens/forms/reports, document business rules Database Activities: Develop Conceptual Database Model, update enterprise conceptual model - Conceptual Design - Finalize work on proposed reports, forms, screens, web pages, etc - Application flowcharts - Entity-Relationship Diagrams (ERD) - User views, entities, business rules solidified

SDLC: Planning

Project Activities: Feasibility Study, Form Project Team Database Activities: Review DBMS Options, Assign Database Specialist to Team Define goals Define plan Measure expected Return on Investment (ROI)

SDLC: Support

Project Activities: Respond to Reported Problems, Apply Mandated Changes, Respond to change requests Database Activities: Database performance tuning, database software patches, schema changes to support application changes - In large organizations this phase is tuned over to a support team - Performance, unexpected results, bug fixes and failures all go through a mini SDLC process and are fixed immediately - enhancement requests are normally rolled into next future projects

SDLC: Physical Design

Project Activities: Specify Physical System, Specify Physical Hardware Database Activities: Physical database design - Hardware specifications made. - Database design implemented in database

SDLC: Logical Design

Project Activities: specify logical system software, specify logical hardware Database Activities: Develop logical data model, perform normalization - Logical Design also called internal design - Applications broken in testable modules, specifications, written and process modeling documented with logical flow diagrams between modules. - Normalization of Database

Tuples

Records

Second Normal Form

Requires a database in First Normal Form and further optimizes it. Each nonprimary key attribute is identified by the whole key (called full functional dependency) The requirement that each field value in a table is associated with only one row, is an example of normalizing a database to which form? --No partial dependencies: One or more non-key attributes are functionally dependent on part of the primary key --Find all functional dependencies

Third Normal Form

Requires a database in Second Normal Form and further optimizes it, generally regarded as fine-tuning. If the logical data model contains fields that depend on another non-primary key field, then it is in VIOLATION of the rules of _____. Nonprimary key attributes do not depend on each other (called transitive dependencies) --No transitive dependencies: Functional dependency between two (sets of) non-primary key attributes --Make every determinant a candidate key

New Systems Development

Systems Requirements -> Data Model -> Data Model Transformation -> Database Design

Relations

Tables

Functional Dependencies

The attributes another attribute depends on. EXAMPLE: For an employee list with the primary key of employeeNum and attributes Fname, Lname, deptID. All attributes are dependent on employeeNum because of the employeeNum was different, they would also be different. IE different employees have different employee numbers and names

Foreign Key

The field that is included in the related table so the field can be joined with the primary key in another table for the purpose of creating a relationship. The common field in the related table.

Cardinality

The number of associations that occur among specific things in an entity relationship diagram is called ____

Composite Key

Two or more fields that collectively define the primary key by unique combinations of their values. Real-world object that is existent-dependent on another real-world object. E.g., DEPENDENT is existence-dependent on EMPLOYEE; the PK of DEPENDENT contains the PK of EMPLOYEE

Files (as a database model)

Unlike Files... databases typically come with meta-data with translation from the physical layer and the logical layer - Meta data (data about data) - any information about database objects or database object instances

Data Integrity

Which of the following identifies the quality of data in a database? <--- Answer The correctness of data after processing, storage or transmission.

Systems Development Lifecycle

Which of the following systems acquisition methods requires staff to systematically go through every step in the development process and has a lower probability of missing important user requirements? <-- Answer

Candidate Key

an attribute value that could be used as a primary key for another class Key potential to be a PK

Relational Database Model

data model based on the simple concept of tables in order to capitalize on characteristics of rows and columns of data

Data Integrity: Table-Level

entire table is locked, preventing access to any row by transaction T2 while transaction T1 is using the table. not suitable for multiuser DBMS because T1 and T2 can't access the same table even when they try to use different rows

Many to Many

multiple entities of one type can be related to multiple entities of the same type A student can have many tutors, each tutor can have many students

Row Names

row, record, tuple