Midterm Flashcards
Basics, SQL, Tableau
Typical Database Design Process
- Requirement Analysis
- Modelling
- Create Database Schema
What are entities described by?
- Entity Class
- Entity Instances
- Attributes
Criteria for Primary Keys
- Short
- Numeric
- Fixed
- Unique
SQL
Structured Query Language
ERD
Entity Relationship Diagram
Goals of modelling a database?
- Representation of information
- Describes the data to be contained
- Explain how the data interrelates
Reasons to model:
- Blueprint
- Easy to understand
- Easier implementation
Entity Instances
The occurance of a particular entity (in a relationship), may have many.
Surrogate Key
A DBMS-supplied identifier of each row of a table. Used when a unique identifier cannot be found.
Server
Software specifically designed to provide services to other applications.
MySQL
A database management system (DBMS) implemented as a server.
DBMS
Database management system
Foreign Key
A primary key from another table which is a field in another table and provides a link between the two tables.
Minimum Cardinality
Minimum amount of times an entity must participate in a relationship.
Maximum Cardinality
Maximum amount of times an entity can participate in a relationship.
Composite Key
Consists of more than one attribute, this happens when a single key attribute cannot uniquely identify an entity instance.
Query
A statement that retrieves information.
DATEDIFF
Indicates the difference between the start_date and end_date.
Formatted as: DATEDIFF(date1, date2)
DATE
Contains the date only.
Formatted as: YYYY-MM-DD
DATETIME
Contains both the date and time components.
Formatted as: YYYY-MM-DD hh:mm:ss
DISTINCT
Retrieves unique values.
SELECT
Used to select data from a database.
Normalization
Orginizing data for ease of use, and to minimize redundancy.
Parent and Child Tables
In a one-to-many the parent is the entity on the ‘one’ side of the relationship and the child is the many side of the relationship.
—————-I–I
Mandatory - One (exactly one)
—————l–<
Mandatory - Many (one or more)
————-O–I
Optional - one (zero or one)
————O–<
Optional - Many (zero or more)
Relationships
How different entities interact, usually expressed as relationship sentences.
Identifiers
The primary key. Attributes that can uniquely identify entity instances.
How are Entities Implemented?
As tables (entity brcomes the table name, and attributes become rows or records).
Attributes
Describe the characteristics of an entity.
Entity Class
A collection of entities of the same type.
Entity
Something that users want to track.
