Databases - Tables - SQL - Joins/Union Flashcards
Table
is a unique set of data with a consistent number of typed data attributes, called columns. Each table has a primary key which is a column that uniquely identifies a row.
Where is the primary key? parent or child table
parent
What does the parent key become in the “child” table?
A foreign key which is a column
Ordinality (aka modality)
Represents the parent side of the relationship. It indicates if the relationship between the tables is optional or mandatory/required (i.e. book and book buyer vs. book and writer)
Zero or O means it’s optional
One or more (a perpendicular line) means is required
- Zero or More
- One or more
- One and only one
- Zero or one
Cardinality (aka multiplicity)
Describes the “child” side of the relationship. Can be either:
- 1 to 1
- 1 to many
- Many to many
To implement a many to many relationship, there has to be an associative table or a table that acts as the in-between by serving as a lookup/table of contents to link. Without the associative table, it would not be possible to look up a single row in a many to many relationship.
Spreadsheets
(namely Excel)
A spreadsheet is an electronic ledger, i.e., an electronic version of paper accounting worksheets. It was created to facilitate people who needed to store their accounting information in tabular form digitally.
Commonalities between Databases and Spreadsheets
- Both can contain large amounts of tabular data
- Both can use existing data to make calculations
- Can be used by many users
Differences between databases and spreadsheets?
The difference between these two forms of data storage lies in the way three characteristics are implemented
1. Data contained
Excel - any type of info in a cell (date, integer, string, etc.). Different values and formats possible for cells.
Database - only raw data, single data value per cell, must preset the type of data contained in the field. Otherwise, you might get errors.
2. Storing Data
Excel - you store data in the cell
Databases- store the data in a table, and no formatting available (font, color, size, etc.). All you care about is to save the numbers.
3. Calculations in cells
Excel - different cells contain calculations (functions and formulas)
Databases - all calculations are done after retrieval, can only view
Benefits of Databases over Excel
- Data intengrity
- Not limited to 1M rows of data
- Relations between tables are logically limited in Excel, and slows down the program.
- Being able to control changes - stable structure and controlling access/user restrictions. Changes are visible to everybody instantly.
- Increases efficiency (less duplicates, and easier to update information through related tables)
Entity-relationship (ER) Diagram
One is drawing an Entity-Relationship diagram, an ER diagram for short. As shown in the picture below, the different figures represent different data entities and the specific relationships we have between entities.
Relational Schema
It represents a table in the shape of a rectangle. The name of the table is at the top of the rectangle. The column names are listed below.
All relational schemas in a database form the database schema. You can also see lines indicating how tables are related in the database.
Database management
Database design + creation + manipulation =
Database management
Joins vs Unions
Joins and Unions can be used to combine data from one or more tables. The difference lies in how the data is combined
Joins combine data into new columns - matches columns in one with columns in the other
Unions combine data into new rows (might require same number of columns and same data type).
DBMS
Database management system
Benefit - it creates a separation between the queries from users or other DBMSs from the data stored. The queries communicate with the DBMS
