Database Design, Database Design Flashcards
What is a database?
central source of data meant to be shared by many users for a variety of applications
What is a DBMS?
heart of the database that allows the creation, modification, and updating of the data
What is metadata?
information that describes data
What are Entities/Relationships
any object or event which someone chooses to collect data onassociations between entities
What is a ER Diagram?
Shows relationship between entities
What are Attributes/Record/Keys?
characteristic of an entitycollection of data items that have something in common with the entitya data item(s) in a record that is used to identify a record
What are the 4 types of files?
Master = records for a group of entitiesTable = data used to calculate more dataTransaction = used to enter changesReport = print reports of the data
What are the steps in the normalization process?
1. remove repeating groups (1NF)2. make all attributes functionally dependent on the primary key (2NF)3. all non-key attributes are fully functionally dependent on the primary key AND there are no transitive (non-key) dependencies
What are the steps to Database Design? What are the outputs of each?
1. Requirements Analysis = DB Requirement Specification2. Conceptual Database Design = Conceptual Data Model3. Logical Database Design = Logical Data Model4. Physical Database Design = Physical Data Model
What is the main problem in Database Design?
What are the domain needs?
Examples of Conceptual Data Model?
ERD, Class Diagram (OOD)
What is the Logical Data Model Dependent on?
The DB Tech. that will be used
Examples of Logical Data Model?
Relational Data Model, Network Data Model
What is the Physical Data Model Dependent on?
DB Platform
What is a Relational Data Model?
Uses concept of normalization; collection of related relations
What are the relational DB technologies?
AnomaliesNormalization1NF2NF3NF
What is a conceptual data model?
- Shows data requirements of an organization at a high level of abstraction- Does NOT have implementation details- Includes objects, relationships, constraints
Why are the terms conceptual, logical, and physical used in data modeling?
to differentiate levels of abstraction versus detail in the model
What is a logical data model?
- derived from conceptual data model with implementation included (aka Implementation Data Model)- includes more details- uses commercial DBMS software (Oracle, MySQL)
What is a physical data model?
- uses selected DBMS language to translate logical data model into physical representation within the DBMS- internal storage structure and file organizations
What is an Information Model?
describes the structure of objects in a system--identity, attributes, relationships to other objects and their operations
In order to have a complete and correct Information Model:
Does your model:Capture all the relevant objects and their relationships?Satisfy all the requirements?Satisfy all the requirements efficiently?
One of the main goals in information modeling is to....?
capture the objects and their relationships from the real world that are important to the business under study
What is the construct of a relationships? What are they dependent on?
Connection between thingsBusiness rules
What are the three types of relationships in information modeling?
- Association = instance connection (1:1, 1:m, n;m)- Aggregation = part-sub part correspondence between things- Gen/Spec. = similarity among things
Conceptual Design Phase
1.identifying entities2. identifying attributes and attribute domains for entities3. identifying relationships4. identifying candidate and primary keys for entities5. Create an entity-relationship (ER) diagram.
Database Prototype
Working model of both database and the application that accesses the database
Testing a Database
1. Should be preformed in order to locate and fix errors2. Should follow a carefully designed strategy
Two-tier
Client: User Interface + Business & Data Processing LogicServer: Server-side Validation database Access
Three Tier
Client: User InterfaceApplication Server: Business Logic Data ProcessingDatabase Server: Data Validation Database Access
1NF
No repeating groups. All data values are atomic. Still subject to insertion, deletion and update anomalies.
2NF
It's in 1NF. All non-key attributes are dependent on all parts of the primary key.
3NF
2NF and no attributes that are not part of the primary key are transitively dependent on the primary key.
Database Requirement Document
1. Well defined Mission statement. 2. Operational scenarios
ER Model
Contains rectangles with diamonds intermediates. Conceptual high level diagram of the structure of a database. Provides basic framework and and a user's view of data.
Data Model
a formal expression of data, data relationships, and constraints on the data.
Relationships
usually expressed using verbal expressions within the requirements document
Consistency
The transaction must change the database from one consistent state to another consistent state.
Isolation
The transaction is independent from other transactions. An incomplete transaction should not be visible to other transactions.
Durability
Committed transactions are permanent and should not be lost owing to any ensuing malfunction.
Atomicity
The transaction is a complete unit, and is executed in its entirety or not at all.
Rollback
Undoing a partially completed databases transaction, in which a database is returned to the consistent state it was before the transaction
Confidentiality
refers to the need of an enterprise to maintain control over sensitive data. Security breakdowns of this type can lead to loss of competitiveness, loss of revenues, or any other of many detrimental consequences.
Availability
refers to the fact that the enterprise must have access to its data at all times. Loss of data can have far-reaching, detrimental effects on the enterprise.
Integrity
refers to the fact that data that is not kept secure cannot be guaranteed to be accurate. Corrupt or invalid data is of little use to an enterprise.
Backup and Repair
Creating copies of their database to ensure data access and security. Copying log files to help restore a database.
Integrity Controls
Controls prevent invalid data from being entered into the database. Invalid entries can lead to misleading or incorrect data.
Optimistic Concurrency Control
Read, Validate, Write
Privileges
Type of action a user can take regarding a specific database object.
Serializability
refers to whether a set of transactions must be executed individually, or whether they can be executed concurrently with one another.
Authorization Controls
Mechanisms built into database requiring user IDs and passwords.
Encryption
Database protection technique that encodes data and requires a specific key to decode it.
Commit
Final step in a successful completion of a database transaction in which the database attains a new consistent state.
Views
Mechanism is a powerful security tool that can be used to hide many parts of the database from unauthorized users.
Concurrent transactions only read data
no conflict exists and the order of execution is not significant.
Concurrent transactions read or write entirely different data structures
no conflict exists and the order of execution is not significant.
Concurrent transactions read or write the same data structures
the order of execution is significant.
Deadlock
A situation in which two or more transactions are simultaneously waiting for another transaction to release a lock on a data item.
Decomposition
The process of creating new relations from existing relations based on functional dependencies within the original relation.
Determinant
An attribute or group of attributes on which another attribute is functionally dependent.
Transitive dependency
In a functional dependency, if B is functionally dependent on A, and C is functionally dependent on B, C is then said to be transitively dependent on A.
partial functional dependency
In a functional dependency, when B is functionally dependent on A, and an attribute can be removed from A and the dependency still exists, B is said to be partially dependent on A.
Data Manipulation Language (DML)
SQL subset is used to operate on the data, including retrieval, update, delete and insertion operations
Data Definition Language (DDL)
SQL s subset is referred to as the schema definition language. Used to create relations, domains, views and access privileges in the database.
Data Control Language (DCL)
A language used to control access to data in a database. Define privileges for users of the database
All Privileges
GRANT ALL PRIVILEGES ON Employee TO Emp_Users; orGRANT ALL ON Employee TO Emp_Users;
With Grant Options
GRANT SELECT ON Employee TO emp_00004 WITH GRANT OPTION;
Granting Privileges
GRANT (Options: SELECT, UPDATE, INSERT,etc) ON Employee TO emp_00004;
Granting TO PUBLIC
keyword allows the DBA to grant privileges to all users in one statement.Ex: GRANT SELECT ON Employee TO PUBLIC;
Degree
The number of attributes (columns) in a relation
Operational Scenario
Describes one or more beginning to end transactions involving the database system and it's environment.
SQL INSERT
Used to add records to a database.Ex: INSERT INTO Employee VALUES (00005, 'Jill', 'Jackson', '01-10-80', 's_rep', '1003', '28000');
Diamond
Shape used in a Chen ER model to indicate a relationship between entities
Data Dictionary
Provides:1. Each attribute's domain2. Any default values (if present and permitted) for each attribute3. Attribute's value, if it can be null.
Union-compatibility
Means that they must have the same number of attributes with matching domains
Entities
A uniquely identifiable element about which data is stored in a database. Can be any element about which data needs to be stored. Need not be tangible objects
Utilities criterion
Criteria refer to: performance monitoring, use monitoring, support for database administration, and database tuning capabilities
Transactions criterion
Criteria refer to: backup and recovery capabilities, logging systems, concurrency control, rollback and commit support, and support for stored procedures.
Denormalization
The rejoining of relations that were decomposed during normalization.
System R
Project that led to the Commercial Database (IBM's DB2 and Oracle's SQL/DS) development.
data definition functionality criterion
functionality refers to: enforcement of primary keys, foreign key support, support for data types, domain support, data integrity mechanisms, support for views, data dictionary, data model type, and schema support
Normalization
process of organizing and refining relations within a relational database usually has the effect of reducing the duplication of data items within the database at times reducing the amount of storage space needed for the base tables of the database. Addresses insertion, deletion and update anomalies. Creation of additional tables to achieve these goals.
RESTRICT
Synonymous with NO ACTION.
data mode
A formal expression of data, data relationships, and constraints on the data. Expresses these data relationships to a DBMS
database strategy
Creating a corporate data model is a common activity during the planning.
Update Anomaly
A circumstance in which redundant data in a relation may not be properly updated.
candidate key
1. Least likely to have its value changed2. Least likely to lose its uniqueness3. Contains the fewest characters4. Easiest for the user to use
Insertion anomaly
occurs when certain attributes cannot be inserted into the database without the presence of other attributes.
Lost updates
When simultaneous updates occur to a relation, one update mayoverride another.
Uncommitted updates
problems can occur when one transaction is allowed to see the intermediate results of another transaction.
Unrepeatable query results:
problems can occur if a database query accesses only partially updated data. AKA Dirty Read
timestamp
is a distinct identifier that the DBMS assigns to a transaction.Methods prevent conflicts by rolling back any transactions involved in a conflict and restarting them.