Class Notes (834,991)
Canada (508,850)
Dr.Hurst (27)
Lecture

Access Review.docx

15 Pages
80 Views
Unlock Document

Department
Computer Science
Course
COMPSCI 1BA3
Professor
Dr.Hurst
Semester
Fall

Description
Access Review Relational database management systems (RDBMS) Backstage view- select current/existing dbase or create new (blank or template) Query = “ask dbase a question” = English  RDBMS syntax; Form (input), Report (output) Organizing data • The first step is to identify each entity, and its individual attributes – Entity: a person, object or event • Next, define each entity’s attributes as a set of fields; each Access entity is called a table – Each field (i.e., column) has a specific data-type – A set of field values is called a record (i.e., row) • Enter data values into your table(s) and store the data as an Access 2010 file (.accdb) Formal terms: attributes, Customer relation, tuples. An Access table is a two-dimensional representation of a relation. Databases and Relationships • A group of related tables is properly known as a relational database; Access simply uses “database” • You can connect the records in the separate tables through a common field (also called a join field) • 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 PK from one table as a field in a second table, to form a relationship between the two tables, the field is called a foreign key (FK) with respect to its use in the second table • Other design models for database structure: network, hierarchical • A join operation creates something akin to a result table containing “master records”, in which the join field is often duplicated in the result table • Data Dictionary: Design documentation that describes all pertinent information about the database definitions (tables, fields, data-types, constraints, relationships, keys, etc.) Keys: • Keys involving more than one field are referred to as composite, concatenated or compound keys • A field which has a set of unique values and can therefore be used to “identify” a particular record is known as a candidate key; if a candidate key does not exist naturally, an artificial one must be created (i.e., to become the PK) Restrictions on Keys: • PK must have unique values; no duplicates allowed • PK must not have a null value; a data value is required • For each non-null FK value, a corresponding PK value must exist elsewhere (i.e., in another table in the database) • FK values may be duplicated, but “satellite” data are different for each occurrence; FK can have null value in some instances • NOTE: keys are often, BUT NOT NECESSARILY, used as the basis for sorting the records in a table Types of relationships • Types of relationships among tables: – One to One (1:1) 1:1 is rare in the real world, but is often imposed on a set of relations for design reasons – One to Many (1:N) – Many to Many (N:M) • Examples: – 1:1 … Husband/Wife – 1:N … Parent/Child; Account/Transaction – M:N … Doctor/Patient; Supplier/Customer; Bus/Route; Book/Author • Relationships expressed in English with the help of verbs: – Patient visits Doctor – Producer makes Film – Athlete plays on a Team -Inventory has Products Designing a Relational Database • Consider the “multi-valued” attributes that exist in the following pair of tables (expressed in Relational Notation): – 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; traditional problems associated with accuracy and integrity are avoided through careful analysis of the table relationships; a typical approach is to decompose large tables into smaller, related tables e.g., 1NF: no multi-valued attributes are allowed in tables e.g., 3NF: no transitive dependencies (i.e., non-primary key values depend solely upon primary key values) 3NF is considered the minimal normal form necessary for a working database design Special consideration • Sharing and Security – Isolation • DBA can create Views for individuals/groups, to protect sensitive data (e.g., government records, client data, employee personal data, business operational data) – Locking shared data • classic scenario = Readers & Writers Problem – Protection • user passwords, capability controls, time controls, monitoring – Recovery • backup, transaction logging, checkpoints • Overall database design formally known as a “schema”, views are “sub-schemas” • NOTE: Access uses the term “view” in a very different manner *Datasheet View, Design View are not sub-schemas] • schema” is the overall picture, the overall design (expansion to data dictionary) • Three types of view. View icon- design view, datasheet view Real-Time Operation – Serialization • sequencing of operations, time-stamping of transactions – Update anomalies, Lost Updates • E.g., banking, international transactions, stock markets, library book loans, air traffic control, astronomy, airline or hotel reservations, bus or taxi dispatching – Scientific experiments (real-time data capture) – Time Zones • Overall database design formally known as a “schema”, views are “sub-schemas” • NOTE: Access uses the term “view” in a very different manner [Datasheet View, Design View are not sub-schemas] Relational DBMS • A database management system (DBMS) is a software program that lets you create databases and then manipulate data in them • Three types of view. View icon- design view, datasheet view Creating a table datasheet view • Accept the default ID primary key field with the AutoNumber data-type, or rename the field and change its data-type, if necessary [see note below] • In the Add & Delete group on the Fields tab, click the button for the type of field you want to add to the table (for example, click the Text button), and then type the field name. Repeat this step to add all the necessary fields to the table • Generally wise to avoid using AutoNumber data-type for PK; also avoid accepting Access suggestions for default PK (i.e., explicitly choose your own) Save Save-As is the method used to store a database in an earlier file format (Access-97, Access-2007, etc.) Creating a simple query • A query can be thought of as a question you “ask” about the data stored in the database – E.g., Which Canadian provinces have an average summer temperature above 25 Celsius? – E.g., Which salespersons have sold the most trucks so far this year? • Simple Query Wizard used to quickly select records & fields to form a question • Queries are saved as objects in the Access file • The query is not actually entered in a natural language; several methods available (e.g., SQL, QBE, Wizard, Report) • Typically, a query result is not stored permanently in the database – only the query design is saved as an object (in SQL form) • Can’t have a null value in a primary key, but you can have it in a foreign key. • All information has to be stored in the form of tables, and therefore be accessible through quarries. Access fails this rule does not use relations to store all its information, so It fails this test whether or not if a database is relational meaning close to 100% Access is about 50% near being fully relational. • Relation database system is Access: these are based on a relational model, good determinant is how relation are. Everything in the database is represented by relations, Access is not fully relational. • Readers & writers problem: Protect users from obtaining old data. Example, Hotel reservation we have to be aware there are limited number of rooms and we start booking rooms according to data that is old, so my room may be given to someone else. • SQL-. (Structured Query Language) is a inquires language, saved in SQL form to be retrieved later on. • Edgar Codd developed these rules to determine if it a database is fully relational or not. • Customer ID: is a good example of a foreign key and primary key. Customer ID: is stored as a text. Creating Simple Form • 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, or use the Form tool to create a simple form quickly and easily 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 Another printing option is to use CuteWriter to print to a PDF file Compacting and repairing databases Advanced compaction techniques ensure that objects within files are stored contiguously Contiguously means things are stores next to each other. They are Concentric circles that are magnified. Free list: Very seldom your files get stored contiguously (physically adjacent) Defragmentation: Take up less storage all the gaps are eliminated compacting them into one block (compacting) Protection: You are only allowed certain can view at a time. Capability: limit people how much they can edit. Monitoring: help schedule maintenance, and how well the system performing are searches efficient? Recovery: Backup- Have a physically remote backup 3 layers are a good. Careful with physical destruction. • Checkpoints- snapshots of transactions. Guidelines for setting a field • You must name each field, table, and other object • Choose an appropriate data type • The Field Size property defines a field value’s maximum storage size for Text, Number, and AutoNumber fields only • Byte • Integer • Long Integer • Single • Double • Decimal Creating a table in design view • Creating a table in Design view involves entering the field names and defining the properties for the fields, specifying the primary key, and saving the table structure Specifying the primary key in design view • Click in the row for the field you’ve chosen to be the primary key to make it the active field. If the primary key will consist of two or more fields, click the row selector for the first field, press and hold down the Ctrl key, and then click the row selector for each additional primary key field • In the Tools group on the Design tab, click the Primary Key button Adding a field between two existing fields • In the Table window in Design view, select the row below where you want the new field to be inserted • In the Tools group on the Design tab, click the Insert Rows button • Define the new field by entering the field name, data type, optional description, and any property specifications Importing data from excel worksheet • The import process allows you to copy the data from a source without having to open the source file • Click External Data on the Ribbon • Click the Excel button in the Import & Link group to start the wizard Creating a Table by Importing an Existing Table Structure • Make sure the External Data tab is the active tab on the Ribbon • In the Import & Link group, click the Access button • Click the Browse button • Navigate to the file • Make sure the Import tables, queries, forms, reports, macros, and modules into the current database option button is selected, and then click the OK button • Click the Options button Adding Fields to a Table Using the Data Type Gallery • The Data Type gallery, available in the Add & Delete group on the Fields tab, allows you to add a group of related fields to a table at the same time, rather than adding each field to the table individually • The group of fields you add is called a Quick Start selection Deleting a Field from a Table Structure • In Datasheet view, click the column heading for the field you want to delete • In the Add & Delete group on the Fields tab, click the Delete button or • In Design view, click the Field Name box for the field you want to delete • In the Tools group on the Design tab, click the Delete Rows button Addin
More Less

Related notes for COMPSCI 1BA3

Log In


OR

Join OneClass

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

Sign up

Join to view


OR

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.


Submit