Relational Databases Notes

Explain the importance and advantages of databases and the difference between database and file-based legacy systems.
Explain database systems, including logical and physical views, schemas, the data dictionary, and DBMS languages.
Describe what a relational database is, how it organizes data, and how to create well-structured relational database tables.

Data Integration: Data is integrated.
Data Sharing: Data sharing is facilitated.
Minimize Data Redundancy and Inconsistencies: Reduces data redundancy and inconsistencies.
Data Independence: Data is independent of the programs that use it.
Easy Accessibility: Data is easily accessed for reporting and cross-functional analysis.

External Level: Different users of the database information have logical views of the data.
Internal Level: The physical view of the data, which is how the data is actually physically stored in the system.
Designers: Need to understand users’ needs and the conceptual level of the entire database as well as the physical view.
Logical View: How people conceptually organize and understand the relationships among data items (e.g., a sales manager views all customer information as being stored in a table).
Physical View: How data are physically arranged and stored in the computer system.

To design a database, you need to have a conceptual view of the entire database.
The conceptual view illustrates the different files and relationships between the files.
Data Dictionary: A “blueprint” of the structure of the database, including data elements, field types, programs that use the data element, outputs, and so on.

Data Definition Language (DDL)
- Builds the data dictionary.
- Creates the database.
- Describes logical views for each user.
- Specifies record or field security constraints.
Data Manipulation Language (DML)
- Changes the content in the database.
- Creates, updates, insertions, and deletions.
Data Query Language (DQL)
- Enables users to retrieve, sort, and display specific data from the database.

Represents the conceptual and external schema as if that “data view” were truly stored in one table.
Although the conceptual view appears to the user that this information is in one big table, it really is a set of tables that relate to one another.

Customer Table and Sales Table related by Customer #.
Primary Keys: Uniquely identifies each record in a table.
Foreign Key: A primary key from one table that is used in another table to establish a relationship.
- Customer # is a Foreign key in the Sales table because it is a Primary key that uniquely identifies Customers in the Customer table. Because of this, the Sales table can relate to the Customer table.

Data stored in one large table can be redundant and inefficient, causing the following problems:
- Update anomaly
- Insert anomaly
- Delete anomaly

Every column in a row must be single valued.
Primary key cannot be null (empty), also known as entity integrity.
If a foreign key is not null, it must have a value that corresponds to the value of a primary key in another table (referential integrity).
All other attributes in the table must describe characteristics of the object identified by the primary key.
Following these rules allows databases to be normalized and solves the update, insert, and delete anomalies.

Users may want specific information found in a relational database and not have to sort through all the files to get that information. So they query (ask a question) the data.
Example: What are the invoices of customer D. Ainge and who was the salesperson for those invoices?

Illustrates how to set up a query to extract specific information (Sales Invoice # and Salesperson) for a particular customer (D. Ainge) from related tables (Customer and Sales).