Chapter 7, Section 3: Relational Databases

One-to-many Relationships

- Exists when only one side of the relationship is unique. - Example: Single building may contain many classrooms, but each classroom is inside only one building - Represented in relational databases by two tables linked together by a foreign key. Figure 1: https://www.dropbox.com/s/edwu8ulqbcicq3x/Screen%20Shot%202021-10-19%20at%208.43.39%20PM.png?dl=0 Figure 2: https://www.dropbox.com/s/f6zi06z8ik57lgm/Screen%20Shot%202021-10-19%20at%208.43.13%20PM.png?dl=0

Many-to-many Relationships

- Exists when the relationship between two entities is not unique on either side. - For example: Each student in a typical school has more than one teacher. - Represented in relational databases by linking two tables directly to each other (this would cause redundancy) - Figure 1:https://www.dropbox.com/s/notpd1k3j0iqadw/Screen%20Shot%202021-10-19%20at%208.47.15%20PM.png?dl=0 - Figure 2: https://www.dropbox.com/s/eypgj4d52uqsdtq/Screen%20Shot%202021-10-19%20at%208.47.31%20PM.png?dl=0

One-to-one Relationships

- Simplest kind of relationship - Has two entities or attributes that share a unique connection with one another - Example: School ID numbers - Represented in a relational database by storing both items as fields in the same table - Figure 1: https://www.dropbox.com/s/8dgf7niptitk8b1/Screen%20Shot%202021-10-19%20at%208.38.16%20PM.png?dl=0 - Figure 2: https://www.dropbox.com/s/kxj5h0g63okf5wm/Screen%20Shot%202021-10-19%20at%208.40.38%20PM.png?dl=0

Backup

A command that creates a backup copy of the database

Restore

A command that replaces the broken database with the working backup.

Table

A data structure in a relational database that is comprised of fields (columns) and records (rows). - Tables store the information in a relational database. Each table is comprised of fields (columns) and records (rows). There is no limit to the number of fields and records a table can contain, as long as there's enough storage space on the drive.

Relational Database

A database that holds data on multiple related tables. - Tables and rows are referred to as fields and records in a database. - Rows are called records and columns are fields. -Each record is the combination of all the fields in a table. - Organized information into related tables.

Normalization

A design technique that organizes data in a database in an optimal way. This results in it being easier to add, remove, and update data without introducing errors.

Primary Key

A field in a table that uniquely identifies a record. - Each table in a relational database has one field that is designated as the primary key field. The purpose of this field is to give each record a unique identifier.

Foreign Key

A field that corresponds to the primary key field in another table. - A field that looks similar to the primary key field of the table. - Whenever a foreign key corresponds to a primary key field a relationship called, one-to-many relationships, is created. When a table in a database references records in another table it is using the primary key value of the record it wants to reference. Essentially, a foreign key is a primary key from another table.

Form

A graphical interface that allows users to query a database by filling in information on the form. - Most database users don't write queries in query language directly. Instead, they enter information into a form that builds the query automatically. - A good example of a form is the search bar of a search engine. When you enter search terms and press enter, the information from the form is entered into a query that retrieves the information. - Forms are used in a relational database to build the objects in the fields through manual input. For example, when you fill out a soccer registration form online, the information you type in the form fields is entered into the database.

One-to-Many Relationship

A kind of relationship between tables where a foreign key corresponds to a primary key field.

Query

A method of asking questions about the data stored in a database. - Queries are questions used to retrieve data stored in database. Generally, it's not useful to look at an entire data table. Instead, database users build queries that retrieve the relevant information. For example, if you have ever searched for a single TV show from a streaming service, you have queried automatically.

Primary Key

A primary key is a way of distinguishing a record from other records. They are usually numbers but can be any unique value. For example: A phone number

Normal Form

A set of rules about how data is structured, see database normalization. 1NF: First normal form, (ex: rough filer to large objects like sticks and rocks); each cell contains a single value and each record/row must be unique 2NF: Second normal form (ex: finer filter for sand and large particles); must meet the requirements of 1NF, must be single field that contains a unique primary key for each record 3NF: Third normal form (ex: fine filer that removes bacteria and harmful chemicals); meet all requirements for 2NF, each field is an attribute of the primary entity.

Field

A single attribute of each record in a table. NOT NULL: When a schema of a database prevents a field from being left blank.CONSTRAINT Fields impose limitations on the value that can be entered into the field, allowing you to determine the type and form of information being entered. A method used to avoid Garbage In, Garbage Out (GIGO)

Record

A single instance of any entity stored in a database table.

Intermediary or Junction Table

A table that sits between the teacher and student tables and keeps track of every teacher-student relationship. Used to avoid redundancy in many-to-many relationships Creates a one-to-many relationship with a junction table. Figure 1:https://www.dropbox.com/s/ubpryodstimrv4c/Screen%20Shot%202021-10-19%20at%208.49.58%20PM.png?dl=0 Figure 2: https://www.dropbox.com/s/8pky1fql38alumx/Screen%20Shot%202021-10-19%20at%208.50.20%20PM.png?dl=0

Report

An easy-to-read listing of the results of a query. - A report is a formatted, easy-to-read listing of the results of a query. Without reports, query results would resemble a spreadsheet. When you search for an online video and are presented with a list of relevant options, the list is a report.

Data Definition Language (DDL)

Commands that change the structure, or schema, of a database. 1. Create: New database or table 2. Alter Table: Modify fields, define keys 3. Drop: Delete a table and its data

Data Manipulation Language (DML)

Commands that have to do with records within a database and not the schema itself. 1. Insert: Create a new record 2. Select: Read information from database 3. Update: Change records 4. Delete: deletes records from a table

CRUD

Create, Read, Update, Delete

Databases

Electronic repositories of organized information.

Grant Select To

Gives X permission to run select queries in a database

Deny Drop To

Prevents X from dropping tables from a database.

Relational Database Concepts

Relational databases work by linking fields from different tables to create complex and easily-searchable databases. Advantages of relational databases: 1. Support for concurrent users 2. Scalability 3. Speed of access 4. Support for a variety of data types 5. Security through access control 6. Resiliency through tools for back up and replication 7. Flexibility in allowing user-defined actions 8. Ability to manage the information put in the database 9. Support for a variety of data, such as strings, numbers, and data fields.

SQL (Structured Query Language)

SQL is a computer language used by most relational databases for creating queries. Using SQL, a database user can view, edit, delete, and create database records.

Table Relationships

Table relationships is used when you want to associate data in one table with data in another table (bringing data from multiple tables). You can do this by adding a foreign key field to one of the tables. One-to-many relationships, example: phone number (a phone number can belong to only one person, but a person can have multiple phone numbers).

Database Dump

The process of exporting the database to another kind of program (i.e., Excel) and stores it in a .CSV file.

Schema

The schema is the general structure of a database. The schema defines the logical groupings and relationships between databases objects such as tables and fields.