SQL/MySQL - 02 - BasicDatabaseTerminology

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
      
      
• 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