DOC PREVIEW
MSU FW 893 - LECTURE NOTES

This preview shows page 1-2-3-4-5 out of 14 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Slide 1“Keys”Slide 3Slide 4Slide 5Creating Tables in AccessTable RelationshipsTable Relationships One-to-OneSlide 9Table Relationships One-to-ManySlide 11Many-to-Many RelationshipsRectifying Many-to-Many RelationshipsTable Relationships Rectifying Many-to-ManyProcess of designing “good” databases, and table structures is called NORMALIZATION. This term is based on the mathematical concepts of NORMAL FORMS (First, Second, Third, Boyce-Codd, Fourth, Fifth Normal Forms). We won’t worry about the formal wording of normal forms, but we will use the concepts they embody to normalize our databases.Example definition of Third Normal Form (3NF):“A relation is in third normal form if and only if, for all time, each tuple consists of a primary key value that identifies some entity, together with a set of zero or more mutually independent attribute values that describe that entity in some way”“Keys”•There is a way to uniquely identify each entry in a table. This is called the PRIMARY KEY.To begin, examine each table, and highlight the field or fields that uniquely identify each unit of observation. This is known as the PRIMARY KEY (PK). A critical concept is that each record in a table does not have to have a unique identifier contained in a single field (i.e., each individual doesn’t necessarily have to have a Social Security Number or ID number). A COMPOSITE PRIMARY KEY (CPK) is a unique identifier composed of two or more fields.Shocking Event Table Stream ID Position (above/below) Segment Date Crew Segment Length Conductivity Water Temperature Weather Water ConditionsOften, it is convenient to have fewer (or even a single) fields represent the primary key. This creates some redundancy in the data, but can make it easier to use the database. This is one place where there are no hard and fast rules.Shocking Event Table Stream ID Position (above/below) Segment Date Crew Segment Length Conductivity Water Temperature Weather Water ConditionsShocking Event Table Shocking Event ID Stream ID Position (above/below) Segment Date Crew Segment Length Conductivity Water Temperature Weather Water ConditionsNote that there may be more than set of possible primary keys. In this case, each possible set is a candidate key. Good candidates for primary keys should 1. Uniquely identify each record in the table 2. It can not be null (i.e., contain missing data) 3. It can not be a multipart field 4. Generally fewer fields are better 5. Once set, the value in any of the key fields should rarely if ever be changed 6. If possible, select fields for primary keys that relate directly to the table nameCreating Tables in Access•Design view–type in Field Names–Determine Data Type-Text-Memo (won’t discuss)-Number-Byte, Long Integer, Integer, Single, Double, Replication ID, Decimal-Date/time-Currency-Auto Number (often used for primary keys)-Yes/No-OLE Object (won’t discuss)-Hyperlink-Lookup Wizard (special - discuss use)-Define Primary Key-Define validation rules - use expression builderTable Relationships•Establishing table relationships is the way we draw together data from separate tables. This minimizes redundant data and maintains higher levels of database reliability.•Types of relationships–One-to-One–One-to-Many–Many-to-ManyTable RelationshipsOne-to-OneStudent IDFirst NameLast NameA111A112A113A114A115A116Student IDSocial Security NumberPhone numberA111A112A113A114A115A116Table Relationships•One-to-one relationships are relatively rare in most database structures. Further, one-to-one relationships can be thought of as a specialized case of one-to-many. •The most common type of “true” one-to-one relationship is when one table is a SUBSET TABLE of another. Example- faculty are a subset of all university staff members. Graduate students are also a subset of all university staff. Thus, one way of implementing a personnel database would be to have a staff table (with information common to all staff members such as social security number), and separate faculty and graduate student tables to hold data that applies only to each of these specialized type of staff members. •When you have a one-to-one relationship, the linkage is achieved by having the same primary key in both tables (e.g., social security number in the staff table, and in the faculty or graduate student table).Table RelationshipsOne-to-ManyStudent IDFirst NameLast NameA111A112A113A114A115A116Student ID Class …A111 FW893A111 FW100A111 FW205A113 FW893A113 FW205A114 FW999Table RelationshipsOne-to-Many Example-Many fish are caught at each siteSite Table Individual Fish TableStream Name (CPK) Stream Name (CPK/FK)Position (CPK) Position (CPK/FK)Site (CPK) Site (CPK/FK)Year (CPK) Year (CPK/FK)Month (CPK) Month (CPK/FK)Day (CPK) Day (CPK/FK)Width Fish_ID (CPK)Depth Species CodeTemperature LengthConductivity AgeLinkage is established by taking primary key (PK, or composite primary key CPK) from “One” side of relationship, and inserting that into the “Many” side of the relationship. This now becomes what is known as a “FOREIGN KEY”.Many-to-Many RelationshipsStudent Table Class TableStudent_ID (PK) Class_ID (PK)First Name Class NameLast Name Class DescriptionStreet Instructor_IDCity Max EnrollmentStateZipcodePhoneProblem here is how to link Students with classes. Each student can take multiple classes, and each class has multiple students.Rectifying Many-to-Many RelationshipsEnrollmentStudent Table Table Class TableStudent_ID (PK) Student_ID (CPK/FK) Class_ID (PK)First Name Class_ID (CPK/FK) Class NameLast Name Class DescriptionStreet Instructor_IDCity Max EnrollmentState Enrollment (???)ZipcodePhoneNotice that the Student-Class Table serves to link the Student Table and the Class Table. As such, it has both of the primary keys from each of these tables as foreign keys.The relationship between Student Table and Enrollment Table is One-to-Many (because each student can take many classes, but there is only one Student_ID, Class_ID combination in the Student-Class Table for each studentClass Table and Enrollment Table is One to Many because each student can be enrolled in a class only onceTable RelationshipsRectifying Many-to-ManyStudent IDFirst NameLast NameA111A112A113A114A115A116Student IDClass A111 FW893A111 FW100A111 FW205A113 FW893A113 FW205A114 FW999Class Course TitleInstructorFW100


View Full Document

MSU FW 893 - LECTURE NOTES

Documents in this Course
Load more
Download LECTURE NOTES
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view LECTURE NOTES and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view LECTURE NOTES 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?