OPIM final all handouts PDF

Preview

Summary

rubric eiun iueog io3go 98ht3giu2 iouh...

Description

OPIM 3103 Handout-6 Database Design Data Terminology and Concepts – – – –

Field (Attribute): A single characteristic or fact about an entity Record (Tuple): A collection of fields that describes one instance of an entity Table (Entity): A collection of records Database: A collection of related tables

Primary keys – –

A field or combination of fields that uniquely and minimally identifies each member of an entity A primary key composed of more than one field is called a combination key or multivalued key

The primary key cannot repeat and cannot be blank Below we explain how to design a database that will efficiently store data so that users can query the data and use it for their business needs. Consider a set of data on students, courses and grades, including attributes such as StudentID, Student Name, Major, CourseID, CourseName, and Grade. Figure 1: Students

Figure 2: Courses

Typically, a university might want to use this data to produce grade reports containing the following information (Figure 3): Figure 3: Grade report

How should you store this information efficiently in a database? When storing the data in the database, there are three important requirements: #1. Preserve relationships between data. We should be able to tell from the data which students took which courses, and what grade they got for that course. #2. Store all data. We should store data about all students, all courses, and all grades in the database. We should not discard or lose any data. #3. Avoid redundant storage of data. To the extent possible, there should be no repetition of data – we should not store the same data in multiple places. Doing so i) is redundant (unnecessary) – it wastes space, and ii) potentially increases the likelihood of error - if we want to make changes to the data, we will then need to make each change in multiple places. Let’s look requirement #1. If the university needs to print a report as shown in Figure 3 above, then we might be tempted to take the table in Figure 3 as it is, and save it as a table in our database. However, Figure 3 only contains information about students who did take some courses. What about students who did not yet take any course or courses that do not have students enrolled yet? Storing the table in Figure 3 does not satisfy requirement #2: we need to store information about students Chen, Kumar, Carla and Shawn (records 4, 5, 6, and 7 in Figure 1) who have not enrolled in a course yet; we also need to store information about courses ACCT 202 and MGMT 204 (records 4 and 5 in Figure 2) that have no students enrolled in them yet. Note that even though the table in Figure 3 contains all the fields (StudentID, StudentName, Major, CourseID, CourseName, Grade), this table does not contain all the data! Some of the missing data is located in the tables in Figures 1 and 2. We might be tempted to just add the missing data into the table in Figure 3, as shown in Figure 4 below:

Figure 4:

But now, the table in Figure 4 does not satisfy requirement #3: there is a lot of unnecessary repetition. For example, whenever someone takes ACCT 202, we need to write “Financial Accounting 1” in the CourseName field. If there are 500 students in the university, and if ACCT 202 is a required course, we will end up repeating “Financial Accounting 1” 500 times. Likewise, in the table in Figure 4, the fields Major and StudentName also require needless (redundant) repetition, which goes against requirement #3. To fix this problem, in the database we create three tables (Table 1, Table 2, Table 3) containing data as shown below: Table 1: Students Record StudentID 1 S1 2 S2 3 S3 4 S4 5 S5 6 S6 7 S7 Table 2: Courses Record CourseID 1 FIN 101 2 OPIM 203 3 MKTG 201 4 ACCT 202 5 MGMT 204

StudentName Joe Amy Tim Chen Kumar Carla Shawn

Major Finance MIS Accounting Management Marketing MIS Accounting

CourseName Finance fundamentals Business info systems Marketing management Financial Accounting 1 Managing in organizations

Table 3: Grade report Record StudentID 1 S1 2 S2 3 S3 4 S1 5 S2 6 S3

CourseID FIN 101 OPIM 203 MKTG 201 MKTG 201 FIN 101 OPIM 203

Grade A B+ ABA B

Table 1 is the same as the table in Figure 1. Table 2 is the same as the table in Figure 2. Table 3 is similar to the table in Figure 3, but modified: table 3 contains only StudentID, CourseID, and Grade. The redundant fields (StudentName, Major, and CourseName) have been removed. Primary Keys Note that in Table 1, StudentID is a primary key, as it uniquely identifies each student. In Table 2, CourseID is a primary key, as it uniquely identifies each course. In Table 3. StudentID and CourseID are together the primary key (there can be only one primary key per table, but the primary key can consist of multiple fields), as these two fields together uniquely identify a student’s grade for a course. Table 1, Table 2 and Table 3 together satisfy all three requirements: #1. Preserve relationships between data. We have ensured that relationships between data are preserved by using StudentID and CourseID. What do we mean by “preserving relationships between data”? An example is: we can tell which student took which course, and got what grade, even though student data is in Table 1, course data is in Table 2, and grade data is in Table 3. How did we preserve this relationship? By using the StudentID and CourseID fields. Let’s say someone asked us for Joe’s grade in Marketing Management. If we are looking at this data, we do the following: Table 1: go to the record containing Joe’s name, note his StudentID Table 2: go to the record containing Marketing Management, note the CourseID Table3: go to the record containing this StudentID and CourseID, and look up the corresponding grade #2. Store all data. We have ensured that all data is stored by creating separate tables for students (Table 1), courses (Table 2), and grades (Table 3). #3. Avoid redundant storage of data. We have avoided unnecessary repetition of data by deleting fields (StudentName, Major, and CourseName) from Table 3. Note the importance of the StudentID and CourseID fields. If these fields were missing, then we would not be able to link a student to her course and grade. Note that: (a) StudentID is the field that links a student (Table 1) to her courses and grade (Table 3); (b) CourseID is the field that links a course (Table 2) to students and their grades (Table 3);

In general, when you want to link data in two tables, (1) There must be at least one field that is common to both tables. (There could be more than one field common to both tables.) (2) This field (or fields) should be a primary key in one of the tables. It then becomes a foreign key in the other table. Definition of Foreign Key: A foreign key in a table is a field (or fields) that is a primary key in another table. The data in both tables are linked by matching the data in the primary key and foreign key. This is how relationships among data in different tables are preserved. Observe that StudentID is a primary key in Table 1. In Table 3, StudentID is called a Foreign Key. Likewise, CourseID which is a primary key in Table 2 is called a Foreign Key in Table 3.

How to identify which data goes into which tables Why did we group together (StudentID, StudentName, Major) into one table, and (CourseID, CourseName) in another? Why not (StudentID, CourseName)? Or why not (CourseID, StudentName, Major)? 1. Identify entity-sets. Each entity-set gets its own table. In the students-courses-grades example, there are two entity-sets that are immediately obvious: Students (primary key: StudentID) Courses (primary key: CourseID) Each individual student is an entity, and the set of all students is an entity-set. Each individual course is an entity, and the set of all courses is an entity-set. 2. Identify attributes that describe each entity-set. All such attributes go into the table for that entity set. All attributes that describe a student must be grouped together in the student table. Such attributes include StudentID, StudentName and Major. All attributes that describe a course must be grouped together in the course table. Such attributes include CourseID and CourseName. Also, verify that the primary key functionally determines all other attributes. In the students-courses-grades data, StudentID functionally determines StudentName, and Major. CourseID functionally determines CourseName. This is written as: StudentID Æ StudentName, Major CourseID Æ CourseName 3. For any attributes left over, determine functional dependency. Example: what do we do about grades? Grade is an attribute not just of a student, not just of a course, but of a student and a course combined. The fields StudentID and CourseID together functionally determine Grade. This is written as: StudentID,CourseID Æ Grade

This means that (StudentID, CourseID, and Grade) go into a separate table, with (StudentID and CourseID) together as the primary key. Summarizing, we have three functional dependencies: (1) StudentID Æ StudentName, Major (2) CourseID Æ CourseName (3) StudentID,CourseID Æ Grade Each functional dependency gets its own table. The attribute (or attributes) that functionally determines all other attributes gets to be the primary key.

OPIM 3103 Handout-7 Chapter 1 – Creating a Database and Tables Important Concepts: a) Access interface b) Open and save Access file (*.accdb) c) Lock file: *.laccdb d) Create a database e) Database objects: tables, forms, queries, and reports f) Database table: design view and datasheet view g) Primary keys h) Add, edit, and delete records i) Rename, delete an object j) Backup database k) Filter and sort l) Import external data to a database

OPIM 3103 Handout-8 Chapter 2 – Using Design View, Data Validation, and Relationships a) Data validation, data integrity b) Validation rules. We can use any of the following operators to construct validation rules. These operators can also be used in constructing database queries.

*Note: XOR means exclusive OR. The outcome is true only if one of the operands is true. If both operands are true, the outcome will be false. For example, both A and B are students. If we use a traditional OR, (A=student) OR (B=student) will result in true. But (A=student) XOR (B=student) will result in false. Only if one of A and B is a student, then it becomes true. For this class, the knowledge of XOR is not required.

Examples:

Wildcard characters can also be used:

c) Record validation rule Validation rule not only can be applied to individual field(s) such as shown above, they can also be applied to comparing multiple fields in the same table. For example, if we have a database table that records houses for sale. The field ‘DatePosted’ obviously needs to be before the field ‘DateSold’.

d) e) f) g) h)

Input mask Lookup field Relational database Relationships Referential integrity

OPIM 3103 Handout-9 Chapter 3 – Creating and Using Queries x

Create a query in design view 1. 2. 3. 4. 5. 6.

Go to menu Create; Choose Query Design; Select tables needed for the query; Select fields; Specify criteria; Save the query

x

Specifying query criteria for different data types Data type Example Text = “John” Numeric < 745 Date = #3/5/2013# Boolean Yes/No = No

x

Use the Like comparison operator to specify query criteria using wildcards: wildcards are either ? or * which represent one or more characters, respectively, in a text value. Example:

x

Like “s*e” (“sue”,“space”, “shape”, etc.) Like “b?t” (“bit”, “but”, “bot”, etc.)

Comparison operators: =, , =, , Between, In, Like Examples: Between 2 and 8, In(3, 6, 7), Like “Am??”

x x x x x x x

Unique values in query result Show and Sort options for query fields AND, OR used within a single field or across multiple fields Parameter query: enclose the parameter inside square brackets and place it in the criteria row of the desired field Use multiple parameters in a field Use wildcards in a parameter query Calculated fields: Calculated fields are fields that do not originally belong to any database table we use in the query design. Rather, calculated fields are fields we add during the query. That is, they are values derived from existing fields. For example, in the UConn.accdb database we created in class, there is a field named Age in the ‘students’ table. We can create a

x

x

query to display each student’s age after 5 years, an information that does not exist in the table now. Expression Builder: We can use any Built-In functions such as if statement, math and financial functions, etc. to build expressions in any calculated field. For example, create a query to display all students (names) who major in Finance, as well as an added column to indicate whether the student is Old (> 22) or Young (0.20, “High”, “Low”)...