Class Notes (839,394)
Canada (511,324)
Dr.Hurst (27)
Lecture 5

Notesolutions COMP SCI 1B03 Lecture 5- Access.docx

5 Pages

Computer Science
Course Code

This preview shows pages 1 and half of page 2. Sign up to view the full 5 pages of the document.
Microsoft access, saves your changes automatically. Save the original file and copy it. Use TWO files. Differences between worksheets and databases  Nature of the column; o In a spreadsheet; cells and columns can be all different data types. In other words the cells in a column can be just about any data type you like. o In a datasheet; all the cells in the same column must be the same data type. Definitions: Tuple = Formal term that refers in access the term is called record, and instance of an entity Field is a column, everything in that data column must be the same Attribute is a characteristic of something Row represents a different entity Value is the actual datum (i.e., value of the tire size) Organizing Data  The first step is to identify each entity and its individual attributes o Entity; a person, object or event  Next, define each entity’s attributes as a set of fields; each Access entity is called a table o Each field (i.e., column) has a specific data-type o 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 “databases”  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: o Keys involving more than one field are referred to as composite, concatenated or compound keys o 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: o PK must have unique valuesl no duplicates allowed o PK must not have a null value; a data value is required o For each non-null FK value, a corresponding PK value must exist elsewhere (I.e., in another table in the data base) o FK values may be duplicated, but “satellite” data are different for each occurrence; FK can have null value in some instances o 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: o One to One (1:1) o One to Many (1:N) o Many to Many (N:M)  Examples: o 1:1… Husband/Wife o 1:N… Parent/Child; Account/Transaction o M:N… Doctor/Patient; Supplier/Customer; Bus/Route; Book/Author  Relationships expressed in English with the help of verbs: o Patient visits Doctor o Producer makes Film o Athlete plays on a Team o Inventory has Products  1:1 is rare in the real world, but is often imposed on a set of relations for design reasons Designing a Relational Database:  Consider the “multi-values” attributes that exist in the following pair of tables (expressed in Relational Notation) o HR: Employee, Child’s Name o Library: Titles, 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 of large tables into smaller, related tables o E.g., 1NF: no multi-valued attributes are allowed in tables o E.g., 3NF: no transitive dependencies (i.e., non-primary key values depend solely upon primary key values)  NOTE: 3NF is considered the minimal normal form necessary for a working database design Special Considerations  Sharing and Security o Isolation  DBA can create Views for individuals/groups, to protect sensitive data (e.g., government records, client data, employee personal data, business operational data) o Locking shared Data  Classic scenario = Readers & Writers Problem o Protection  User passwords, capability controls, time controls, monitoring o Recovery  Backup, transaction logging, checkpoints  Overall database design formally known as a “schema”, views are “sub- schemas”  NOTES: Access uses the term “view” in a very different manner [Datasheet View, Design View are not sub-schemas] *** Relatio
More Less
Unlock Document

Only pages 1 and half of page 2 are available for preview. Some parts have been intentionally blurred.

Unlock Document
You're Reading a Preview

Unlock to view full version

Unlock Document

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.