1. Relational Database Essentials
- Database
- relational database
- non-relational database
- regards more complex systems + mathematics & programming
- relational database
- main goal: organize huge amounts of data that can be quickly retrieved
2. Databases vs. Spreadsheets
- Speadsheets
- an electronic ledger
- an electronic version of paper accounting worksheets
relational databases | Spreadsheets |
can contain a large amount of tabular data can use existing data to make calculations are used by many users | |
pre-set the type of data contained in a certaiin field | |
all calculations and operations are done after data retrieval | different cells can contain calculations (functions and formulas) |
provide a stable structure, controlling access permissions and user restrictions | finding out who changed or deleted information incorrectly |
- relational databases: storing and keeping track of data
- retrieval of data
- updating of dta
- efficiency
- data consistency
- data integrity
- speed
- security
- Spreadsheets: extensive analysis
3. Database terminology
- Database design
- plot the entire database system on a canvas using a visualization tool
- Entity-Relationship(ER) diagram
- Relational Schema: an existing idea of how the database must be organized
- plot the entire database system on a canvas using a visualization tool
- Database creation
- use SQL to set up the database physically
- Database manipulation
- allows you to use your dataset to extract business insights
- database management = database design + creation + manipulation
- database administration: maintenance of database
- database administrator is the person providing daily care and maintenance of a database.
Relational Schemas: Primary Key
- primary key
- a column(or a set of columns) whose value exists and is unique for every record in a table is called a primary key
- each table can have one and only primary key
- may be composed of a set of columns
- are the unique identifiers of a table
- cannot contains null values
- the primary key is always underlined
Relational Schemas: Foreign Key
- identifies the relationships between tables, not the tables themselves
Relational Schemas: Unique Key & Null Values
Differences | Primary Key | Unique Key |
---|---|---|
NULL VALUES | no | yes |
NUMBERS OF KEYS | 1 | 0, 1, 2 … |
APPLICATION TO MULTIPLE COLUMNS | yes | yes |
- you would need a unique key in a data table whenever you would like to specify data in a given field.
Relationships
Relationships
- relationships tell you how much of the data from a foreign key field can be seen in the primary key column of the table the data is related to and vice versa.
types of relationships
- one-to-many type of relationship:
- Customers to Sales
- many-to-one type of relationship
- Sales to Customers
- many-to-many
cardinality constraints 基数约束
Relational schemas
- represent the concept database administrators must implement
- depict how a database is organized
- = blueprints, or a plan for a database
- will help you immensely while writing your queries