ITGS Unit 1 Definitions: Databases

Define report design view.

Report Design View is in Access. It is where one is able to set up what a report looks like.

Define report print view.

Report Print View is in Access. It is where one can view a report before printing it.

Explain why it is important to use different data types.

1. Different data types can be used to control the memory size of the data being inputted. Memory space is expensive, so being able to minimize the amount of space needed for each field is an efficient away to save money. 2. Different functions can be done with each type, so the data should be in the correct type to access those functions. For example, data in the Number data type are able to do Sum and Average functions.

Describe how to create a database based on a 1:1 cardinal.

1:1 cardinals need to merge the tables into one table, or add a foreign key to the less - important table. For example, a kindergarten student uses 1 cubby and a cubby has 1 kindergarten student.

Describe how to create a database based on a 1:M cardinal.

1:M cardinals need to add a foreign key to the MANY table. For example, an album has many songs but a song only appears on 1 album.

Define the drop-down list technique.

A Drop-Down List is a data validation technique. It counteracts potential spelling errors when typing by making the user select an option from a drop-down menu rather than type in an answer by themselves.

Define flatfile databases.

A Flat-File Database is a type of database. It consists of just one table and because the data is not divided into multiple tables, so new records either cannot be created, updated or deleted; or they will unintentionally lose information and/or insufficiently perform the operation. This causes insertion, update and deletion anomalies. In these flat-file databases, more than one piece of information may be stored in a field, which makes sorting and searching difficult. As well there may be a lot of repeated information or data redundancy, which is a waste of memory and storage. It is difficult to update, add new records, delete records, search, sort and summarize (use aggregates) on flatfile databases.

Define relational databases.

A Relational Database is a type of database. It consists of many tables, related using foreign keys and primary keys. One piece of information is stored in each field, making sorting and searching easy. As well, except for foreign keys, there is no data redundancy. This causes new records to be easily created, updated and deleted without any anomalies. This also causes the data storage to be efficient.

Define boolean operator.

A boolean operator is a type of operator used in a query's where clause to place complex restrictions on item selection. 'And', 'Or' and 'Not' can be used for this type of operation. 'Not' requests the record's values to be the opposite of the condition placed and can be used in the first line of the where clause, while the other two must be used in the second line or more.

Define composite key.

A composite key is a type of key field in a table. It is often used to create relationships in relational databases. It consists of two or more fields that, when put together, can uniquely identify a record.

Define data entry form.

A data entry form is a database feature. It is a screen used to input data into a database. Each record is viewed individually and can be used as menus to help users navigate.

Define data type.

A data type is the format of data in a field. It determines the amount of memory allocated to the field and the possible functions that can be done with the field. For example, a field with the Number data type can only hold numbers and can be manipulated to perform functions such as Sum and Avg. → Format of Data in a Field → Determines Amount of Memory Allocated and Possible Functions

Define DBMS.

A database management system is a type of software used to create, insert, change, delete and retrieve data from a database. The three parts of a database are the user interface, the SQL engine and the set of tables in the database. Access is an example of a database management system.

Define field.

A field is a column on a table. It is a single category of data of a single record. It classifies the different types of data stored. An example is a StudentID field which contains the Student ID numbers in a Student table. → Columns on Table → Single Category of Data on a Single Record → Classifies Different Types of Data Stored

Define foreign key.

A foreign key is a type of field in a table. Used to create relationships between tables, it links the primary key of one table to a field in another table.

Define macro.

A macro is a database or spreadsheet or word-processing function that automates a database process. It is useful for repetitive tasks and is an easy way of programming for novices because in database management systems such as Access, macros are already "scripted" and can be chosen from a list of possible programming commands. For example, in Access, the "Add New Action" drop-down list has various pre-programmed options to choose from, such as "CloseWindow" and "OpenForm". → automates a database process → easy, already scripted, for repetitive tasks

Define nested SQL.

A nested SQL is an SQL statement that uses an SQL aggregate statement to form its 'Where' clause.

Define parameter query.

A parameter query is database query. In this type of query, the criteria is decided by the user at run-time. For example, in Access, [ ] is used around a question to create a pop-up where the user can type in a value.

Define primary key.

A primary key (or a keyfield) is a type of field in a table. It is a unique field that is used to differentiate a record in a table from another, so that data is easy to find and never gets lost. (ie. Student Number)

Define query.

A query is a database function. It is the method of extracting data from a database that matches specific criteria. This can be done using wizard or, more commonly, SQL.

Define record.

A record is a row on a table. It is a collection of fields that represent information for a single entity. For example, a record in a Student table may have a student's ID number, phone number, and address. → Rows on a Table → Collection of Fields to Represent Single Entity

Define secondary key.

A secondary key (or alternative key, or index) is a piece of a table that is used to quickly search for data. It takes up a lot of memory and requires a lot of maintenance. Therefore, tables should not have a secondary key unless they are frequently searched.

Define subform.

A subform is a database widget that is added to a form. It can set up a one-to-many relationship on a form. Examples include buttons and labels.

Define table.

A table is a piece of a database. It is a collection of records representing a single type of item. Multiple tables make up a database. For example, a school database may have a table for attendance, teachers, students, and more. → Multiple Tables = Database → Collection of Records → Represents Single Type of Item

Define aggregate.

An aggregate is an SQL function that summarizes data in a field. Examples of aggregates are avg( ), count( ), max( ), min( ) and sum( ).

Define anomaly.

An anomaly is a common error in flatfile databases. When the user tries to update, insert or delete from a flatfile database, the operation either cannot occur or the data is negatively impacted. Good relational databases do not commonly get anomalies. An example of an anomaly is if the math teacher for Functions changed in a school and all of the student's who have that teacher must have their schedule information changed. With a flatfile database, one must go through the entire database and individually update every single time the teacher name has to be changed. By doing this, spelling errors may occur or some changes may be missed, therefore, there is a lot of anomalies that may occur. → common in flatfile databases → insertion, deletion, update anomalies → operation either cannot occur, or data is negatively impacted → relational databases can prevent anomalies

Define operator.

An operator is a query feature. It restricts the records that are selected in queries by using Boolean expressions to determine whether or not to select a record. For example, <> means "not equal to", <= means "less than/equal to", >= means "greater than/equal to, < means "less than" and > means "greater than". → query feature → use Boolean expressions to restrict records

Define cardinality.

Cardinality is the type of relationship between two tables. It is the number of records in one table in relation to the number of records in another table. This determines how the tables are divided for normalization. These relationships can either be 1:1, 1:Many or Many:Many. → type of relationship between two tables → number of records in one table in relation to another → for normalization

Define the character check technique.

Character Check is a data validation technique. It ensures that a field only contains the character types that are specified. For example, if someone were to input data into the StudentIDNumber field, which is a Number data type, and they include letters; there is an error because they are only able to input numbers.

Define the check digit technique.

Check Digit is a data validation technique. A field whose binary has an even number of 1s will get appointed with a 1 in the beginning and a field whose binary has an odd number of 1s will get appointed with a 0 in the beginning. If, for example, the data gets copied somewhere else and a binary that is supposed to have a 1 comes out to have a 0, there is an error.

Define CSV.

Comma-Separated Values (CSV) is a file format for transferring data. It stores fields and records in a plain text file, separated by commas. This creates small files.

Define the consistency check technique.

Consistency Check is a data validation technique. It checks if two pieces of mutually - dependent data has information that correlates. For example, if a user has indicated that they live in Ontario, the postal code that would be inputted in another field should be an Ontario postal code. If it is not, then there is an error.

Define data entry form.

Data Entry Form is an Access object that allows a user to see one record at a time and edit it appropriately.

List 9 examples of data types.

Data Types include Text (long, short), AutoNumber, Memo (note to self), Date/Time, Currency (which is formatted as money, but stored as a number), Number, Yes/No (this binary type is used for fields which are able to contain 1 of 2 options), Hyperlink and OLE Object (an image). → Phone numbers are short text because they include dashes and parentheses. → Long text takes up the most amount of space.

Describe how data can be inputted.

Data can be inputted by typing it in (such as making a status update on Facebook), importing from another database (such as Blue Kai's database, which sells people's information to companies) and using automatically - generated databases (such as a cell phone, which automatically logs all of its calls).

Define data maintenance.

Data maintenance is a database function where one can insert, update and delete records in a database. However, field names cannot be changed. For example, data maintenance can be done using a data entry form.

Define data validation.

Data validation is a database function. It checks to ensure that the data is in the correct format and, therefore, ensures data integrity by identifying and reducing errors. If there is an error, an error message is given. An example of a type of data validation is Range Check, which checks that the amount of characters inputted by the user is the correct amount of characters the field should have.

Define and Describe E-R diagrams.

Entity - Relationship Diagrams are diagrams to design databases. These are used to normalize the data to create a relationship database. Entities/tables are stored in boxes, attributes/fields are stored in ovals, key attributes/keyfields are stored in underlined ovals and relationships are stored in diamonds. → Name attributes are always blown up to First and Last → Address attributes are always blown up to Street, StreetNumber, City, Province and PostalCode → to design databases and normalize data

Define exporting.

Exporting is a database operation that allows data from a specific table to be outputted into an Excel, CSV or TSV file.

Define the field size technique.

Field Size is a data validation technique. It checks that the number that is stored in a specific field type is within the range that the field type allows. For example, the Short Text field would not allow for a lot of text.

Define grouping.

Grouping is a query or report feature. Written as the "group by" clause in SQL, this function creates subcategories of records to create subtotals for group analyses.

Define importing.

Importing is a database operation that allows data from Excel, CSV and TSV files to be added to the database. It can also create new tables to store information.

Define the input mask technique.

Input Mask is a data validation technique. It ensures that the only specified characters can be entered into the field and shows the user the intended format for the field. For example, YYYY/MM/DD could be used for birthdays.

Define the length check technique.

Length Check is a data validation technique. It checks that the length of the information stored in a field is within the allotted range. For example, a MasterCard number can only have a certain number of digits.

Define the operation of linking tables.

Linking Tables is an operation to create relational databases. In Access, this is called a relationship (the foreign is linked to the primary key). In SQL, this is done in the 'Where' clause.

Define the lookup technique.

Lookup is a data validation technique. It uses a list of values to limit what can be entered into a field. It is also useful in foreign keys. An example is a 'Gender' field, which has options that are limited to 'male' or 'female'.

Describe how to create a database based on a M:M cardinal.

M:M cardinals need to create a new table that contains the primary keys from both tables (in the form of foreign keys). For example, a teacher has many students while a student has many teachers.

Define main menu form.

Main Menu Form is an Access object that has buttons and macros which make a form into a navigation tool to access other objects. → buttons, macros, navigation

Define normalization.

Normalization is a database design process. It is the process of converting a flatfile database to a relational database. By converting a flatfile database to a relational database, the database becomes easier to manage and more useful in finding information and performing functions.

Define the presence check technique.

Presence Check is a data validation technique. It checks if a field is filled in, and if it is empty, there is an error. For example, when ordering something online, the address field must be filled in and the order will not go through unless it is.

Define the range check technique.

Range Check is a data validation technique. It ensures the field is between a certain range of values by setting a range of amount of characters that the field should fit into. For example, a Student number in the Peel District School Board would have to have 6 numbers in it. If it has more or less than 6 numbers, there is an error.

Describe the SQL format.

Select __________ From __________ Where __________ [And/Or/Not ________] Group By ______ Order By ______ Expanded: Select [field names, in the desired order, separated with commas and possibly written with the use of aggregates] From [table names] Where [field name, which may have to include the table name as well; used to pull out information based on the conditions] [boolean operator] [value or other field name] And/Or/Not [field name, which may have to include the table name as well] [boolean operator] [value or other field name] Group By [field name to find subtotals, which would also have to be in the select clause so that the name of the group it is operating with is printed] Order By [field name, used to sort field names alphabetically or numerically]

Define sorting.

Sorting is a query feature. Written as the "order by" clause in SQL, this function orders the records based on the requested field. It is ordered numerically increasing for fields with numbers, and increasing by the alphabet for fields with letters.

Define SQL.

Structured query language (or SQL) is a third-generation language that allows users to easily manipulate databases. An SQL has five clauses; SELECT, FROM, WHERE, GROUP BY and ORDER BY.

Define TSV.

Tab-Separated Values (TSV) is a file format for transferring data. It stores fields and records in a plain text file, separated by tab characters. This creates small files.