Database Purpose
To organize and keep track of things.
Spreadsheet
Effective at keeping track of a single theme, for example, student test scores
... [Show More] in a course.
Brainpower
Read More
Previous
Play
Next
Rewind 10 seconds
Move forward 10 seconds
Unmute
0:01
/
0:15
Full screen
What does a Database allow?
Keeping track of multiple themes. For example; supplies, purchase orders, shipments, inventory items, sales orders, customers, payments, etc.
What are the principles of Databases?
1. Every table has a unique identifier (PK).
2. The value of PK cannot be empty.
3. The order of records (rows) is not important.
4. The order of columns (attributes) is not important.
5. Everything must be recorded only once.
Tables/Relations
A group of records related by a common theme. Very similar to spreadsheets.
Relations
A formal name for a table.
Fields/Columns/Attributes
The column headers.
Rows/Records/Tuples
Hold desired information for each entry.
Primary Key
-A column or group of columns that identifies a unique row in a table.
-Every table must have one of these.
-Must be unique (cannot repeat)
Combined Primary Key
Sometimes more than one column is needed to form a unique identifier. For example: In a table called City, this would consist of a combination of City and State columns.
Foreign Key
A non-primary key in one table that is related to a primary key of a different table.
Resources
Companies' assets that participate in business processes (such as Inventory, Cash, Equipment, etc.)
Events
What is going on in the business processes. Ex: Sales orders or Purchase orders.
Agents
Participants of events, for example customers, suppliers, organizations, departments, etc.
What does 'Designing a Database' mean?
-Define table and their names.
-Define relationship between the tables.
-Define cardinalities of relationships.
-Define fields (attributes/columns) for each table: for example primary keys, foreign keys, non-key attributes.
-Define attributes names and data types.
ER Diagram
-Oftentimes called top-down approach.
-Uses REA model to create entities, which is represented by a rectangle.
Entities Relationships
-If from the business process description it follows that two of these are related (i.e. interacting) with each other, such relationship is depicted on the diagram as a line connecting these two entities.
Cardinalities of Relationships
A property of a relationship. It characterizes to what degree entities participate in a relationship.
1-to-1
-If one record of table A corresponds to exactly one record of table B, and one record to table B corresponds to exactly one record of table A.
-In the two tables, two records always exactly match. In that case, we can stick the records to be one (merge the tables), so this type of relationship is not typical between tables.
1-to-M
If table A is related to table B in this way, then PK of table A should be inserted into table B as a foreign key.
-If one sales zone can have many sales persons serving it, and one sales person serves only one sales zone.
M-to-1
-If one record of table A corresponds to exactly one record of table B, and one record of table B corresponds to many records of table A.
-If one sales zone is served by exactly one sales person, but one sales person can serve more than one sales zone.
M-to-N
-If one record of table A may correspond to many records of table B, and one record of table B may correspond to many records of table A.
-If one sales zone can be served by many different sales persons, and one sales person can serve more than one sales zone.
-Need junction table
Bridge/Junction Tables
-Used because M-to-N relationships cannot be handled by a DBMS.
-Primary keys of the two original tables must be inserted into this as foreign keys, and they form a combined primary key.
-Breaks N-to-M relationship into two 1-to-M relationships.
INSERT Query
-Inserts a new record into a table.
Example of INSERT Query
Ex1: INSERT INTO tableName (attribute1, attribute2,...) Values (value1,value2,...)
Ex2: INSERT INTO CLIENTS (ClientName, StreetAddress, City, State, ZIP) VALUES ('Qwerty, Ltd.', '111 Side Street', 'Blacksburg', 'VA', '24060').
UPDATE Query
Updates existing record in a table
Examples of UPDATE Query
Ex1: UPDATE tableName SET attribute1=value1, attribute2=value2,... WHERE primaryKey=value.
Ex2: UPDATE CLIENT SET Contact='Bill Rogers', Contact_Phone='(540)123-4567' WHERE ClientID=3
DELETE Query
Deletes an existing record from a table
Examples of DELETE Queries
Ex1: DELETE FROM CLIENTS WHERE ClientID=3
-This will erase record 3 from the table. Only possible because the absence of records in other tables that reference this record. If other tables reference, then not able to delete due to referential integrity.
SELECT Query
-Allow database users to pull information contained in one or more related tables together.
-Joins multiple tables, so this is a powerful tool for creation of reports.
Select all fields from one table example
SELECT * FROM CLIENTS
-Provides all rows and all columns from table CLIENTS in a table-like form, called dynast (because the results of the [Show Less]