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