2.1 Database Design
Terms in this set (52)
In Database design, what is a model? What is it used for?
Representation of reality that retains only selected details
- Powerful tool for validating necessary details
- Eliminate irrelevant details
- Associate for map, elements in reality to elements in model
What is an E-R Diagram? What does it do?
Entity-Relationship Diagram / Model
- Visualization (diagram) of the data
- Describes the logical database
- graphical method of mapping the real world
- Once built it is Blueprint for the real database
When was the E-R Model introduced?
1976 by Peter Chen
What does a E-R Model consist of?
What is an "Entity"? What Shape is used to display?
Represents a discrete object (person, place, thing or event). Something that users want to track.
At Wester (Student, Course, Program)
In Business (Order, Customer, Salesperson)
What is an "Attribute" What shape is used to display?
Describe properties of the entity
Student attributes (Student #, Name, Address, Date of Birth)
Order attributes (Order #, Date, Amount)
Note: Relationships can also have attributes
What is a "key" attribute? How is it displayed?
Uniquely identifyes the instance of each entity. Sometimes referred to as an "identifier"
example: SIN, Student numbers, Employee numbers
Identified by the underlining of the text in the oval
What are "Instances"?
Database contains instances of the entities; a set of data items that exist (like a record item)
What are "relationships"? Examples.
Associations between two or more entities. It captures how the entities are related to one another
PERSON works for COMPANY
STUDENT takes COURSE
ARTIST performs a SONG
Can "Relationships" have attributes? Explain with example.
Relationships can also have attributes (ex. Hire date for person is a detail that describes the relationship)
What is "Cardinality"?
Indicates the number of instances of the entities that are involved in the relationship.
How many categories are their for Cardinality? What are they? Where do we find / mark them
1:1 Relationships - single entity to single entity
1:M (or M:1) - single entity to many entities
N:M Relationships - Many to Many
The number / letter of relationship is placed by the relationship line (ex. 1, M, N)
Give an example of a 1:1 Relationship
Facility is the chair of a Department
- One FACULTY MEMBER can be chair of only <b>one<b> dept
- Each DEPT can have only <b> one <b> chair
Give an example of 1:M Relationship
Student registered in a Faculty
- STUDENT is only registered in <b>one<b> FACILTY
- FACULTY can have <b>many<b> STUDENTS registered
Give an example of a N:M Relationship
Students taking courses
- STUDENT can take <b>many<b> COURSES
- COURSE can be taken by <b>many <b> STUDENTS
What is "Participation"? How is it shown?
There are 2 categores.
Total Participation - "All" instances are involved in the relationship (double line)
Partial Participation - Not all instances are involved (single line)
What does a square shape represent in an E-R diagram?
What does an Oval shape represent in an E-R diagram?
Attributes. If they are a key attribute, the text will be underlined
What does an diamond shape represent in an E-R diagram?
Explain the following diagram.
[Faculty] --------<is chair of>====[Department]
Faculty Participation = 1, not all faculty members are chairs of a dept.
Dept Participation = 1, Dept can only have 1 chair
Single line (partial) for participaton of FACULTY. Not all faculty members are chairs of a dept
Double line (Total participatoin) for Dept. All depts must have one chair
With E-R Diagrams, what is Crow's Foot Diagram?
Rectangles represent entities
Relationships shown by lines
Crow's foot (cardinality)
- Forks at end of lines indicates more than 1 relationship
- Vertical line means at least 1 entity of that type
- Small oval means entity is optional
With the E-R Diagram, what is process for creation of Database Design?
- Table for every entity
- Key Attribute is primary key of table
- Attributes of entity become columns in table
- Tables normalized to single theme
- Represent relationships between tables
- Add foreign key to one or more tables
What is content
Broadest sense it is property - intellectual property. Can be protected by copyright, patent, etc.
What does DBMS stand for
Database Management Systems
What does CMS stand for?
Content Management System
What is the purpose of a database?
To keep track of things that involve more than one theme
What is a key?
A key is a column or group of columns that identifies a unique row in a table.
ex. Student number is the key of the Student table
What are foreign keys?
Keys of a different (foreign) table from which they reside.
What are relational databases
Databases that carry their data in the form of tables and represent relationships using foreign keys.
What is metadata?
Data that describes data.
example: Columns in database - Field name, data type, description - describe the data in the field
What is a database management system?
A database management system (DBMS) is a program used to create, process and administer a database.
What are some popular DBMS?
DB2 (from IBM), Microsoft Access, SQL Server (from Microsoft) and Oracle
What is an open source DBMS?
What is the difference between a DBMS and a database?
DBMS is a software program, a database is a collection of tables, relationships, and metadata
How does a developmer modify and existing table?
Developer opens the metadta form for that table and adds a new row of metadata
What four operations that DBMS use to process a database?
read, insert, modify or delete data
What does SQL stand for? What is it?
Structured Query Lanquage - an international standard language for processing a database. It can also be used to create databases and database structures
What other secondary functions do DBMS do regarding databases?
They can administer other activities such as setting up a security system for a database. They can also backup database data, remove data that is no longer wanted or needed, etc
What is a database application?
A collection of forms, reports, queries and application programs that process a database.
A database may have one or more applicat5ions and each application may have one or more users.
These applications have different purposes, features and functions but they all process that same inventory data sotred in a common database
What is "lost-update problem"?
When 2 people are updating the same record and one of the updates is lost
How is "lost-update problem" prevented?
Use of some sort of record locking is used to coordinate the activities of users.
What are the two broad categories of DBMS?
Enterprise DBMS (large scale - many users, many database applications)
Personal DBMS (small scale- smaller, simplier database applications)
What is "normalization"?
The process of converting poorly structured tables into two or more well-structured tables.
The goal is to construct tables that have a single topic or theme.
What is one for for normalizing for data integrity?
Data integrity problems occur when data is duplicated. Best way to prevent is to eliminate duplicated data. Can be done by taking data in one table and splitting into two
What can a database application consist of?
Forms, Reports, Queries, Application programs that process a database
What function can an application program serve for a database and the web?
Application programs serve as an intermediary between the web server and the database
In access what is a Datasheet View used for? What is a Design View used for?
Datasheet View - used to add, modify, delete and view records
Design View - used to create and modify the fields in a table
In Design view, which key switches between upper and lower panes
What are two ways to enter data into the database?
Data can be entered in Datasheet view or you can create a Data entry Form
For a form in Access what are the 3 different views and what are they used for?
Form View - displayes completed form, used to enter or modify data
Layout view - used to create or modify form, visual representation
Design View - used to create or modify form, more options for controlling form
What is the difference between report headers and page headers?
Report headers are only printed once.
Page headers printed at top of every page
In an access report, what are the two type of controls?
Bound - tied to an underlying data source
Unbound - not tied to an underlying data source