CIS 300 Test Review Exam 1- Access

1. CIS 300 Test Review Exam 1- Access Computer Resource Center

2. Database Design Basic Terminology: • A table consists of data that is arrayed in rows and columns. • A row of data is called a record. • A column of data is called a field. Thus, a record is a set of related fields. The fields in a table should be related to one another in some way.

3. Basic Terminology • Primary key fieldis a field in which each record has a unique value. e.g. The SSN • Foreign key is the primary key of another table. e.g. suppose we need to link the employee table with the Hours worked table for payroll purposes. The Employee ID number (EIN) is the primary key of the employees table but the foreign key of the hours worked table. *Every foreign key must be associated with a primary key in another table.

4. Database Design Database design concepts: Entities an entity is a tangible thing or an event. It is a person, place, thing or concept about which data can be collected. Consider the following examples: • The database of a video store would have one entity named video and another named customer (These are physical entities). • Organizations incur expenses from paying hourly employees and purchasing materials from suppliers. Hours worked and purchases are event entities in the database of most organizations. • The library lends books for free. If you were to think of checking out a book as a sales transaction for zero revenue, how would you handle the revenue generating event? The event entity here is the number of checkouts.

5. Database Design • Attributes- a characteristic of an entity. These attributes become the table’s field. • E.g. what are the attributes for the entity “Customer”? Customer ID, First name, Surname, Date of Birth, Address and Phone no. • What are the attributes for the entity “Fashion Model”? Name, Height, Weight, Dress size, Hair color and Eye color.

6. Relationships • In a one-to-one relationship, one instance of the first entity is related to just one instance of the second entity • In a one-to-many relationship, one instance of the first entity is related to many instances of the second entity, but each instance of the second entity is related to only one instance of the first entity • In a many-to-many relationship, one instance of the second entity is related to many instances of the second entity, and one instance of the second entity is related to many instances of the first

7. Relationships (Examples) • You have a specific driver license # that matches only to you *One to One • 1 Employer has many Employees; 1 Guitar has many Guitar Strings;1 Car has many Seats *One to Many • A book has more than one author, and each author writes many books. *Many to Many

8. Editing Relationships Referential Integrity- a set of rules Access enforces to maintain consistency between related tables when you update data in a database. -By using cascading referential integrity constraints, you can define the actions that the Server takes when a user tries to delete or update a key to which existing foreign keys point. -In order words, what happens when you delete or update a cell that has corresponding relationships with other cells? -Should they also delete? Should they be updated every time primary key changes?

10. Cascade Delete • With Referential Integrity on: • If you turn ON Cascade Delete • Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows that contain those foreign keys are also deleted • If you leave Cascade Delete OFF • When deleting primary key, receive an error message explaining cell cannot be deleted due to related cells. • https://www.youtube.com/watch?v=LuC06E6CueI

11. Cascade Update • If you turn ON Cascade Update • Specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key are also updated to the new value specified for the key. • If you leave Cascade Update OFF • When updating primary key that has already established relationships with foreign keys, receives error message. • https://www.youtube.com/watch?v=B6mLVHXM7g4

12. Parent-Child, “Orphans” • Parent-Child relationships exist when a foreign key (child) belongs to a primary key (Parent) • Referential Integrity exists to prevent “children” from losing their connection to “parents” • When a primary key (“parent”) is removed without referential integrity, the child is now an “orphan”

13. Basic Terminology • A form is a database object that is created from an existing table to make the process of entering data more user-friendly • A query is the database equivalent of a question that is posed about data in a table (or tables). • Queries can be designed to search multiple tables but these tables should be connected by a join operation.

14. Types of Queries Select Query • Queries that answer a question • “Selects” relevant data from database records • Helps you get just the data you need in a Datasheet view Append Query • Adds a record to the end of the table • Works by selecting records from one or more data sources and copying them to an existing table

15. Types of Queries Update Query • Designed to change data in records • Often updates information in real time (think online purchases and updating the on-hand amount of that item) • Begins as a “Select” query, then begins by clicking “update” Delete Query • Deletes entire records from a table • Works similar to update query (must start with select query)

16. Use Update or Delete?

17. Database Design Rules • Rule 1: You do not need a table for the business The database represents the entire business. Thus in the practice example* The library is not an entity. *practice problem at the end of tutorial A. • Rule 2: Identify the entities in the business description In our example the entities are Members, employees and books.

18. Database design rules • Rule 3: Look for relationships among the entities look for relationships between entities; one-to-many and many-to-many. In our example: one-to-many: a member can check out more than one book. • Rule 4: Look for attributes of each entity and designate a primary key. Attributes of members: name, DOB, phone no., email address, member ID card number …etc. Employees: name, # of hours worked, job title,…etc. Books: name, authors, type, status, member ID card number…etc.

19. Database Design Rules • Rule 5: Avoid data redundancy you should not include extra (redundant) fields in a table. Redundant fields take up extra disk space and lead to data entry errors because the same value must be entered in multiple tables. Rule 6: Do not include a field if it can be calculated from other fields A calculated field is made using the query generator as we will see later. ** You should realize the importance of accuracy, case sensitivity…etc in designing databases.

20. Metadata & Hierarchy of Data • Metadata: Data about data. Metadata describes how and when and by whom a particular set of data was collected, and how the data is formatted. A text document's metadata may contain information about how long the document is, who the author is, when the document was written, and a short summary of the document. • Hierarchy of Data:refers to the systematic organization of data, often in a hierarchical form. Data organization involves fields, records, files and so on.

21. Example of Hierarchy of Data

22. Data Dictionary • Data Dictionary: In database management systems, a file that defines the basic organization of a database. • A data dictionary contains a list of all files in the database, the number of records in each file, and the names and types of each field. • Most database management systems keep the data dictionary hidden from users to prevent them from accidentally destroying its contents.

23. Practice Questions • #6 Create a parameter query that shows the hours employees have worked. Have the parameter query prompt for the week number. The output headings should be Last Name, First Name, Week #, and hours. This query is for non-salaried employees only.N.B. you have 3 tables.Employee  Last Name, First Name, Employee ID, Street Address, City, State, Zip, Date Hired, Us Citizen.Wage Data  Employee ID, Wage Rate, Salaried.Hours Worked  Employee ID, Week #, Hours.

24. Answer to practice query #6

25. Practice Questions • Given the fields Quantity and Unit Price, perform a calculation within a query to determine the price of an item if it is now discounted by 10%. Create a new field called Discount Price. • Answer: Discount Price: [Unit Price]*0.9 Note: This is the same as multiplying by 90% or subtracting 10% but the query does not recognize the % symbol and we have to devise an alternative expression.

26. Quiz Questions • (Database Concepts) The metadata for a database describes which of the following properties of a database? • It defines the fields in the database tables • It defines the structure of the database tables • It defines the sizes and formats of the fields in the database tables • It identifies the primary keys • All of the answers provided are correct

27. (Database Concepts) Because it is relatively common and everyone has one, the date of birth is often used as a primary key in a database table for identifying employees within a corporation. True False • (Database Concepts) What is the term for a collection of related records, such as the scores for all of the students in a given section of CIS300? • Field • Character • Item • Table • Query

28. Scenario • You have a table of medicines. One of them is now banned. Create a query with the new requirements. • Fill in the Blank. This is a/an ____________ Query Delete

29. (Database Concepts) Which of the following is the smallest piece of data that can be stored within a database? (select at least one, but no more than two answers) • bit • byte • character • field • record • table

30. (Database Concepts) Which of the following terms is often used to refer to the columns shown in a database table? (select at least one, but no more than two answers) • row • data element • metadata • field • record • data type

31. (Database Concepts) A field within a database is defined as a single item of data that is common to all records and occurs as an individual _____ within a table. • cell • column • row • value • entry

32. (Microsoft Access) The Report Wizard is an easy to use feature in Access that guides you through a series of questions and then generates a customized report based on the answers you provide. • True • False

33. (Microsoft Access) Which of the following types of fields is used to produce a value from an expression or function? • Calculated • AutoNumber • OLE Object • Indexed • Number

34. (Microsoft Access) Which of the following objects provides a simple approach for less-experienced users to extract information from a database? (select at least one, but no more than two answers) • Forms • Macros • Modules • Queries • Reports • Tables

35. While a database itself is a collection of several related files, the program used to build databases, populate them with data, and manipulate the data is called a(n) • _DBMS – Database Management System. • There are two overall approaches to maintaining data: the traditional file approach—which has no mechanism for tagging, retrieving, and manipulating data—and the ____, which does have this mechanism. • Database Approach • Data Approach • Datafile Approach • Indexed file approach

36. A ________ is a unit of information in a ________ • A. record, data source • B. data source, field • C. record, field • D. field, record

37. What do you call a primary key field included in another table? • A. Parent key • B. Child key • C. Foreign key • D. Index