Textbook Notes (367,876)
Canada (161,461)
Dr.Hurst (18)

access ppt+text

8 Pages
Unlock Document

Computer Science

1.1 Organizing Data • Your first step in organizing data, is to identify the individual fields, for each entity – Entity: a person, object, or event • Next, you use Access to define each particular set of fields as a table, one table per entity – Each field (i.e., column) has a specified data-type – A set of field values is called a record (i.e., row) • After entering data values into your table(s), you store the data as an Access 2007 file (.accdb) -a table is a collection of fields that describe a person, place, object, event or idea -the specific value, or content, of a field is called the field value Databases and relationship -a collection of related tables is called a database, or a relational database - common field, a field that appears in both tables Tables and Relationships • A primary key (PK) is a field, or a collection of fields, whose values uniquely identify each record in a table • When you include the primary key from one table as a field in a second table, to form a relationship between the two tables, the field in the second table is called a foreign key (FK) • You can connect the records in the separate tables through a common field (often known as a join field), which often turns out to be the PK-FK combination • Concatenated Key (also Compound, Composite): terms used to describe a multi-field key Relational Database Management Systems • A database management system (DBMS) is a software program that lets you create databases, and then manipulate the data contained in them • In a relational database management system (RDBMS), data are organized as a collection of relations • Access 2007 relations are called tables Exploring the Microsoft Access Window -microsoft access window is the program window that appears when you create a new database or open an existing database -datasheet view, which shows a table’s content as a datasheet in rows and columns, similar to a table that you create in excel -navigation pane is the area on the left side of the window that lists all the objects in the database, and it is the main control center for opening and working with database objects -access creates the ID column as the primary key field for all new ables -the add new field column is the column in which you create the first new field for your table Renaming the default primary key field -right click the primary key column>select rename column Changing the data type of the default primary key field -the data type determines what field values you can enter for the field -access assigns the AutoNumber data type to the default ID primary key field -text data type, allows field values containing letters, digits, and other characters, and which is appropriate for identifying numbers, such as contract numbers, that are never used in calculation -field selector or a record selector selects that entire column or row, which you then can manipulate Entering record -pencil symbol(left of the first field) indicates that the record is being edited -star symbol identifies the second row as the next row available for a new record 1.2 Navigating a datasheet -navigation buttons ,provide a way to move vertically through the records Creating a Simple Query • A query can be thought of as a question you “ask” about the data stored in a database – E.g., Which Canadian provinces have an average summer temperature of more than 25 degrees Celsius? – E.g., Which salespersons have sold the most trucks so far this year? • The Simple Query Wizard allows you to quickly select records & fields to form your “question” Creating a Simple Form • The Form button is found in the Create tab • A form is an object you use to enter, edit, and view records in a database • You can – design your own forms – use the Form Wizard (step-by-step) – use the Form Tool (one click) -form tool creates a form containing all the fields in the table or other database object on which you’re basing the form -layout view, you can make design changes to the form while it is displaying data, so that you can see the effects of the changes you make immediately Creating a Simple Report • A report is a formatted printout (or screen display) of the contents of one or more tables in a database • The Report Tool places all the fields from a selected table (or query) on a report, making it the quickest way to create a report Printing a Report • Open the report in any view, or select the report in then Navigation Pane • To print the report with the default print settings, click the Office Button, point to Print, and click Quick Print or • To display the Print Dialog Box and select the options you want for printing the report, click the Office Button, point to Print, and then click Print (or, if the report is displayed in Print Preview, click the Print button in the Print group on the Print Preview tab) Viewing objects in the navigation pane -All tables, which groups objects according to the tables in the database Managing a data base -ensure that they operate in the most efficient way, that the data they contain is secure, and that you can work with the data effectively Compacting and repairing a database -compacting a database rearranges the data and objects in a database to decrease its file size, thereby making more space available on your disk and letting you open and close the database more quickly -when you compact a database, access repairs the database at the same time Backup and Restoration of a Database • Backing up a database is the process of making a copy of the database file to protect your database against loss or damage • The Back Up Database command enables you to back up your database file from within the Access program, while you are working on your database • To restore a backup database file, you simply copy the backup from the drive on which it is stored to your hard drive (recent data are lost) -store the backup copy in a different location from the original ------------------------- Types of Relationships • Types of relationships among tables: – One to one (1:1) – One to Many (1:N) – Many to Many (M:N) • Examples – 1:1 … Husband/Wife – 1:N … Parent/Child, Account/Transaction – M:N … Doctor/Patient, Supplier/Customer, Bus/Route, Book/Author Designing a Relational Database • Consider these two, field scenarios: – HR: Employee, Child’s Name – LIBRARY: Title, Author • Normalization – a process used by designers to eliminate undesirable anomalies that can occur during the daily management of the database; this requires analysis of the table relationships e.g., elimination of multi-valued attributes is the first requirement, known as First Normal Form (1NF) Special Considerations • Sharing & Security – DBA can create Views for individuals/groups (formally called Sub-schemas) – Protection of sensitive data (e.g., government records, business operational data, client details) • Real Time Operation Update anomalies, lost updates, time zones e.g., airline or hotel reservations, bus or taxi dispatch, international transactions, scientific experiments -------------------------------------------------- 2.1 Guidelines for Designing Databases -identify all the fields need to produce the required information -organize each piece of data into its smallest useful part -group related fields into tables -determine each table’s primary key-composite key( 2 primary keys -include a common field in related tables -avoid data redundancy -determine the properties of each field Assigning field data types -data type determines what field
More Less

Related notes for COMPSCI 1BA3

Log In


Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.