Class Notes (834,049)
Canada (508,296)
Lecture

Access and Info- lectures.docx

28 Pages
73 Views
Unlock Document

Department
Computer Science
Course
COMPSCI 1BA3
Professor
Anthony Hurst
Semester
Fall

Description
Midterm 2- Review- Power Points and Lectures: Access: Power Point 1 • relational database management systems (RDBMS) • Query = “ask dbase a question” = English  RDBMS syntax; Form (input), Report (output) • Navigation Pane – database objects; Datasheet View – direct entry of fields and records • Organizing Data- The first step is to identify each entity, and its individual attributes -Entity: a person, object or event- attributes as a set of fields, each entity is a table -Each field (i.e., column) has a specific data-type -A set of field values is called a record (i.e., row) -Fully relational • An Access table is a two-dimensional representation of a relation. • 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 • Foreign Key and Primary key data must be the same- the name of the field does not need to be the same. Same values in different tables. When you have a field that is a primary key in one table, and it exists in a second table, and it is not the entire primary key in that second table, then it is a foreign key. • 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 Data describing table tools • 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 • -Relationships are connected through foreign keys. Sequences of characters are called strings. Concatenated means joining two fields together to form a primary key. Candidate key- unique values. Keys are unrelated to sorting, although we do sort things based on primary key values because they are unique. Foreign keys exist on how tables are related. • 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 • Relationships expressed in English with the help of verbs: -Patient visits Doctor, Producer makes Film, Athlete plays on a Team, and Inventory has Products -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) -Note: 3NF is considered the minimal normal form necessary for a working database design - What it takes to determine if a relational data base is fully relational, 12 rules. No data base is 100% fully relational. The relational data base system uses tables (relations) to represent everything (ALL*) that is in the data base file. This is not the case in access. All the information has to be represented in tables. Change view/Toggle View, -Data Sheet View or Design View. The set of properties is shown in the general table in Design. You can change general properties so that relational data bases follow the rules. Etc. no null values. SQL allows us to create queries that question the data-base. All info must be represented in tables (MIDTERM QUESTION TIP) to be fully relational. When you cannot fit the amount of properties into fields, you create another table. Alt key relation- multivalued attributes (both in the primary key so they are unique). • 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 (writers are whoever can change the information) (readers are users using the databases) When reader is reading while writer is updating- lost update -Protection: user passwords, capability controls, time controls, monitoring- updates on software etc • 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 [View button toggles- Datasheet View, Design View are not sub-schemas] • Real-Time Operation -Serialization- order of operations -Sequencing of operations, time-stamping of transactions- such as short-selling (stock market investing ex.) -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 • The design table is not a table like a table in the data base • E.Codds 12 rules, one important rule- that all information has to be represented through tables, through relations in a relational database system for it be to fully relational. http://en.wikipedia.org/wiki/Edgar_F._Codd (test material) • • Three types of user: end-user, programmer, DBA- data base administrator • SQL- what we use to create queries. Coded form (server Query Language) • Note: Generally wise to avoid using AutoNumber data-type for PK; also avoid accepting Access suggestions for default PK (i.e., explicitly choose your own) • Create Tabs: • • • Queries: -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) • Forms: -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 • • 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 • • / • Compacting a database rearranges database objects and data to decrease the file size • -Advanced compaction techniques ensure that objects within files are stored contiguously- physically adjacent -FAT- File allocation table- list of occupied areas of the disks (clusters, sectors of tracks) the FAT lists your files and writes where they exist. Files end up getting broken up- fragmentation. Data base file grows, records keystrokes deletions etc, you must compact the database. -Note: Always backup the backup. Power Point Lecture 2: Building a Data Base and Defining Relationships • SQL- view gives you select query link, we use normal English statement for access rather than excel • Useful Query- making a new table with data within other tables to make a whole new table such as earning: (wage*hours worked) • Two delimiters- comma and tab separate each field • OLE- object linking and embedding- embed spreadsheets in another spreadsheet, linking means linking externally, change one spreadsheet the other is updated when opened, memo is handy for a long descriptive thing. OLE is useful in having a field that brings up a picture file. • BLOB- Binary Large Object, excel file word file or picture etc. • Always change Required Value in data view to YES for primary key • Why not check cascade delete? Some case you do not check- find out why- • Edit relationships, enforce referential integrity, cascade update related fields- update relationships updates the keys in the tables. Cascade deletes related records- deleting records in the relationship table will not delete the original primary key table’s values. • 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 • In the Field Name box, type the name for the field, and then press the tab, data type, field size, description, and caption. Update -Use the Field Properties pane to type or select other field properties, as appropriate • Table Relationship: • • • 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 – Select what data to import • Adding Fields to a table using : • 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 • One of the most powerful features of a relational database management system is its ability to define relationships between tables -You use a common field to relate one table to another • • A one-to-many relationship exists between two tables when one record in the first table matches zero, one, or many records in the second table, and when one record in the second table matches at most one record in the first table -The Primary table is the “one” in a one-to-many relationship -The Related table is the “many” table • Referential integrity is a set of rules that Access enforces to maintain consistency between related tables when you update data in a database -The Relationships window illustrates the relationships among a database’s tables -Click the Database Tools tab on the Ribbon -In the Relationships group on the Database Tools tab, click the Relationships button Information Systems Power Point 1: • Hint: review where to put graphics -Ballistic- Non ballistic mode refers to a person that is somewhat relaxed, more open and more suggestible, Advertisers like audiences in non-ballistic mo
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