Study Flashcards
Where is the user’s data stores in a database?
Tables
Stored in tables
User data
Information about the structure of a database is called
Metadata
Metadata
Information about the structure of a database
What is a DBMS
Database Management System is software used to create, access, and maintain a database
Software used to create, access, and maintain a database
DBMS
Retrieves data from the database
DBMS engine
What does a DBMS engine do
Retrieves data from a database
When asked to add data to a table what is done?
Insert into query
Insert into query is used when?
Adding data to a table
Referential integrity constraint
Rule that states each foreign key value must match a primary key value in the other relation
Rule that states each foreign key value must match a primary key value in the other relation
Referential integrity constraint
What do rows and columns represent
Tuples and attributes
Tuples and attributes are represented by
Rows and columns
BOYCE Codd normal form
If all determinants are a candidate key
If all determinants are a candidate key
BOYCE Codd normal form
Third normal form
Free from transitive dependencies
Free from transitive dependencies
Third normal form
Second normal form
No partial dependencies
No partial dependencies
Second normal form
What is a composite key
Primary key made up of two or more attributes
Primary key made up of two or more attributes
Composite key
How is “multi determines” written?
Double arrow
Double arrows are used for
Multi determinates
DML
Deals primarily with user data (INSERT, SELECT)
Deals primarily with user data (INSERT, SELECT)
DML
DDL
Deals primarily with metadata (CREATE, DROP)
Deals primarily with metadata (CREATE, DROP)
DDL
How is a composite attribute (an attribute with more than one value) written in a relation?
Only its values are
Only its values are written in a relation
Composite attribute
How are open circle attributes (derived) written in a relation?
They are not
Simple vs composite
Simple has one piece of data, composite has more than one attribute
Single vs multi valued
Single has one data value, multi has more than one
Stored vs derived
Stored data is retained, derived are calculated
One piece of data
Simple attribute
Has more than one attribute
Composite
One data value
Single attribute
More than one data value
Multivalued attribute
Retained
Stored data values
Derived
Calculated
Represented by rectangle in Chen and Crow’s Foot Models
Entity class
Entity class is represented by
Triangle in Chen and Crow’s Foot Model
ER model other names and creator
E-R Diagram or ERE. Peter Chen
E-R Diagram or ERE. Peter Chen
ER Model
Relational Model
Applied using relational algebra. Stored as rows and columns. Uses normalization
Applied using relational algebra. Stored as rows and columns. Uses normalization
Relational Model
Low Level
Physical level (file layouts, indexing, OS access strategies) DBMS specific
Physical level (file layouts, indexing, OS access strategies) DBMS specific
Low Level
Representational Model
Relational Model
UOD
“Universe Of Discourse”. Description of model we are implementing in database.
“Universe Of Discourse”. Description of model we are implementing in database.
UOD
Database development lifecycle
Create data model, transpose data model into relations, normalize relations, create database tables within DBMS, define relationships between tables, define forms, define queries, define reports, define menus, application metadata, maintenance.
Create data model, transpose data model into relations, normalize relations, create database tables within DBMS, define relationships between tables, define forms, define queries, define reports, define menus, application metadata, maintenance.
Database development lifecycle
Run time subsystem
Processes components created with design tools
Processes components created with design tools
Run time subsystem
Design tools subsystem
Creates tables, forms, queries, and reports
Creates tables, forms, queries, and reports
Design tools subsystem
Database vs filesystem
Special programs needed to extract filesystem data, DBMS used for database. Data items frequently duplicated in filesystems, minimal duplication in databases. All programs must update filesystem, DBMS handles file modification. Convert files to compatible formats, DBMS does all formatting. No data relations in filesystems, databases have this.
Define database
Shared, self describing, organized collection of logically-related persistent data
Shared, self describing, organized collection of logically-related persistent data
Database
Data formats
Text, numbers, date/time, graphics and images, sound a video, programs and other objects
Text, numbers, date/time, graphics and images, sound a video, programs and other objects
Data formats
Application metadata
Data about structure of forms, reports, and queries
Data about structure of forms, reports, and queries
Application metadata
Index (and overhead) data
An index aids in searching
User data
Data tracked by users
Data tracked by users
User data
DBMS definitely
Software that facilitates the transfer of data between the database and the design and run-time subsystems
Software that facilitates the transfer of data between the database and the design and run-time subsystems
DBMS
Database disadvantages
DBAs, installation costs, conversion costs, backup, political problems
DBAs, installation costs, conversion costs, backup, political problems
Database disadvantages
Define data model
Set of concepts that can be used to capture a UoD
Set of concepts that can be used to capture a UoD
Data model
What is an entity instance?
Actual occurrence of data for an entity class like “Smith, John”. Usually never shown
Actual occurrence of data for an entity class like “Smith, John”. Usually never shown
Entity instance
How is a composite identifier represented on a Chen Diagram?
Identifier with multiple lines pointing out of it
Identifier with multiple lines pointing out of it
Composite identifier
Types of anomalies
Redundancy is repeated information. Update anomaly is failure to change all instances of a specific value. Delete anomaly is to delete data and lose other values as a side effect. Insert anomaly needs user to add information about multiple themes.
Redundancy is repeated information. Update anomaly is failure to change all instances of a specific value. Delete anomaly is to delete data and lose other values as a side effect. Insert anomaly needs user to add information about multiple themes.
Types of anomalies
A data model that effectively captures the entities and the interactions/connections between those entities will
Require little to no modification when transposed into relations