CMPS100B: Introduction to Technical Computing for the Sciences PDF

CMPS100B : Introduction to Technical Computing for the Sciences Lecture 2: Introduction to Access Part 1 Department of Computer Science Dhofar Univ...

CMPS100B : Introduction to Technical Computing for the Sciences Lecture 2: Introduction to Access Part 1 Department of Computer Science Dhofar University The content of some of these slides have been adapted from: Microsoft Official Academic Course MICROSOFT ACCESS 2016 Download link: https://www.dit.ie/media/ittraining/msoffice/MOAC_Access_2016.pdf Topics Introduction to databases Creating tables Using datasheet view Using design view From templates Setting the primary key Creating relationships 2  Data and Information What is the difference between data and information? 3 Why is Information Important? Why is Information important for every individual and organization? For decision making [everyday and strategic planning] What kind of information do you need for Buying a new cell phone Running a hospital Managing a big bank e.g. Bank Muscat How do we store (large amounts) of data? 4  What is a Database A database is an organized collection of data. A database is a structured set of data held in a computer, especially one that is accessible in various ways. A database is a collection of information that is organized so that it can easily be accessed, managed, and updated. 5 Layton McCulloch (2016), Databases Retrieved from https://prezi.com/2etgnswygrsu/databases/ Examples of data that can be included in a Database Enterprise Data of interest University Student data, courses, employees, … Manufacturing Product data, suppliers, employees, …  Self-check: Find data that can be included in the DBs of the mentioned enterprises Enterprise Data of interest Hospital Medicine, Staff.. Bank Money, Staff.. Car renting Car… 6  Tables, Records and Fields A database is a set of related tables Tables consist of a set if records each consisting of fields Fields Records 7 Tables relationships 8  Definitions Table: A table is a collection of data about a specific topic, such as students or contacts. To store data only once for a specific entity, we use a separate table for each topic The objective is to make the database more efficient, and to limit data-entry errors. Record: A collection of data about a given entity. Field: A category of information related to all records (in a column format). Value: A single piece of data. 9  Primary Key Primary Key: The Primary Key is defined as one or more fields that contain values uniquely defining records. Why do we need a Primary Key? Each table has many attributes Example: table student has as fields: idStudent, FirstName, LastName, DateBirth, city… Can 2 students have the same first name? Can 2 students have the same date of birth? Can 2 students have the same idStudent? 10 More on Primary Key A primary key is a field that will contain no duplicates and no blank values. Each table should have a primary key A primary key can be one field or more Cannot be same! That’s why you removed the Primary Key 11 Exercise: Suggest the primary key 12  Planning a Database Identify the Purpose of the Database What do you want the database to tell you. What kinds of data will be in the database. Describe who will use the database. Review Existing Data Existing databases, spreadsheets, documents, invoices, etc. Determine Fields Develop fields to generate information that is pertinent to the database. Fields should contain smallest meaningful value. 13  Planning a Database Group Fields into Tables A table should contain information on ONE SUBJECT. Too many tables is not a problem, too few is. Determine Primary Key The Primary Key is a combination of Fields that contains values that uniquely define records. 14 Creating a new database Access 2016 Blank Database Open Access Click on Start Click All Programs Click Microsoft Office Click Access When the page opens, locate and click Blank Desktop Database 1. The name of the DB file 2. The location of the DB file 3. Create 16 New Table The new table opens looking like the illustration above. As with all other Office 2016 products, all of the database functions are accessed through the Ribbon Click the Create tab to create a table 17 Creating a New Table Click Create Click Table Design 18  Creating table structure To create a new table in the design view: Add fields’ names Change Fields data types Main data types Short Text: Can store up to 255 characters of text, and numbers (or a combination of both). Number: Stores numeric data that can be used in mathematical calculations. Date Time: Stores date and/or time data. Currency: Stores monetary data with precision to four decimal places Auto number: Unique values created by Access when you create a new record. Yes/No: Stores Boolean (true or false) data 19 The Primary Key The Primary Key is a combination of Fields that contains values that uniquely define records. It is possible to set multiple Primary Keys is a table. Relationships require a Primary Key to be set. Once you designate a primary key for a table, Access will control data entry preventing any duplicate records (repeated records) or Null values (Empty values) 20 Types of Primary Keys AutoNumber: If you want the primary key to be automatically set by MS-Access It consists in adding a sequential number whenever a new record is added. It is the simplest way to create a primary key. Single-field primary keys: If you have a field that contains unique values such as ID numbers or license plate numbers, These fields can be used as primary keys. Multiple-field primary keys: Sometimes, the uniqueness of a single field cannot be guaranteed,  designate two or more fields as the primary key. This is commonly done when a table is used to relate two other tables in a many-to-many relationship. Databasedev.co.uk (2015), Database design Retrieved from 21 http://www.databasedev.co.uk/database_design_requirements.html  The primary key To define the primary key 1. Select the required field(s) 2. On the Design tab, in the Tools group, click the Primary Key button. The Primary Key button is highlighted.  A key icon appears on the idStudent row to designate the field as a primary key 22 Adding records Click Save button Provide the name of the table 23 Adding records Switch to DataSheet View Add records one by one 24 Relationships  Types of Relationships One to One: An association between two tables in which one record a table relates to only one record in the other table. E.g. Tables Passport and Person One to Many: An association between two tables in which one record in a table can relate to many records in the other table. E.g. Tables Student and Advisor Many-to-Many : An association between two tables in which one record in either table can relate to many records in the other table. E.g. Tables Student and course 26 Father/child (or one-to-many) relation One student has one advisor but one advisor has many students One child has one father but one father has many children Add Advisor table’s primary key as a field in Student table to setup a relationship 27 Father/child relation Given these two tables, who is the advisor of Ali? 28 Father/child relation Solution: we add a new field idAdvisor to table student  Foreign Key What is the name of Ali’s advisor? 29 Many/many relation One student can take many courses and one course can be taken by many students So it is a many to many relation Solution: To link student and course, we need a new table “register” (or student_course…) which takes the primary key of the two tables 30 Many/many relation Which courses are taken by student Ali? Is it a father/child relation? 31 Many/many relation 1- Which courses are taken by Ali ? 2- Who is enrolled in Web Design Course ? 32 Creating Relationships Close all tables Click the Database Tools tab Click Relationships Add all tables Close the “Show Table” windows 33 Creating Relationships Drag and drop the primary key to the foreign key 34 Creating Relationships Check the following - Enforce referential integrity - Cascade update related fields - Cascade delete related records 35 Relationships 36  Relationships A relationship works by matching data in key fields - usually a field with the same name in both tables. In most cases, these matching fields are the primary key and a foreign key. A foreign key indicates how the tables are related in the other table. If you want to change the Primary Key, you must first delete (remove) the relationship between the tables. 37 References Layton McCulloch (2016), Databases Retrieved from https://prezi.com/2etgnswygrsu/databases/ Databasedev.co.uk (2015), Database design Retrieved from http://www.databasedev.co.uk/database_design_requiremen ts.html 38