part #1 access 2016 database conceptsis.cba.edu.kw/240/access exercise/database introduction power...

Created By Eng. Osama Mustafawy, Copyright 2019

Part #1 ACCESS 2016 DATABASE CONCEPTS

• What is a Database??– A database is defined as an organized collection of data (information).

– A sheet of paper divided into columns and rows or a table in MicrosoftWord or Excel would previously have been considered a simpledatabase.

– Single table databases are very limited in their uses and would not beconsidered as a business solution.

• Why not Use Excel??– There are many types of data you may need to store and manage—

text and numbers, for example. Depending on what you want yourdata to do for you, you may or may not need to use a database. Youmight be able to use a spreadsheet program like Microsoft Excel.

– Excel is a spreadsheet software program that is traditionally used tomanage numerical information, like totaling up all purchases by onecustomer. While it can store some types of text-based data—like acustomer's name and contact information—it's not really what Excelwas designed to do.


• Excel Example: Customer list andorder tracking– You can even filter customer information to display all of the

customers who live in a particular state, like in the following image.Additionally, you can sort data to order it in a particular way.

– However, if you want to see very specific results in your data—likehow many orders a single customer placed in a year—Excel is not asefficient as Access at providing you with that data.



• Excel Example: Data Entry– If you use an Excel spreadsheet to track your orders, each time a

customer places an order you would have to enter a new row ofinformation in the spreadsheet. This would likely include thecustomer's name and address. If that customer orders from yourcompany more than once, information would have to be entered eachtime. Your spreadsheet would contain redundant information.


As you can see in the image above, customers Tonya Bullock and McKenzieGrant each placed several orders on different days and for different books. Theircustomer contact information was entered every time they placed an order. This isthe limitation of spreadsheet software such as Excel because it is a single,flat file.


• Access Example: Customer list andorder tracking– Microsoft Access is designed to manage information. Access allows you

to enter a client's name, address, and phone number the first time acustomer places an order. This information is entered into an Accesstable, which is designed to hold basic customer information on clients.

– In addition to the table with customer information, you would probablyalso want a table with information about the products you sell andanother table to hold data related to specific customer orders.



• Access Example: Customer list andorder tracking– Access is called a Relational Database Management program

because the tables are linked—or related—as you can see in theimage below.

– In this example, the Customer Info and Orders tables are linked byCustomer ID and Book ID.




• What is a Relational Database??– A relational database is able to store and manipulate

data across multiple tables.

– By utilizing multiple tables, a relational databasereduces unnecessary duplication of data.

– For example, when using a large database to storecustomer information, including addresses, it is likelymany individuals will live in the same street and shareother common parts of the address. Rather than storingthe same street name, city, area, etc, over and over,this information can be stored in a separate table. Aseach new customer is added to the database, only theunique house number needs to be recorded.

– Example or Relational Databases are Oracle, MS SQL,Access, DB2 and Sybase.



• What is a Table??– Tables are used to store the data in a structured and

organized format. As previously mentioned, tables aresimilar to those of Word and Excel, in that they containrows (records) and columns (fields).



• What is a Record (Row)??– A record applies to data entered into a single row of a

table. All data in that row would belong to an individualor item.

– A record would normally include a unique ID number,Name, Description, plus other relevant information. Witheach new record, a new row is created.



• What is a Field (Column)??– Each column (previously known as a ‘field’) is used to

define what specific information is to be entered intothat particular column.

– A column titled ‘Street’ for example would signify this tobe the point in a record to enter in the Street namewhen creating each new record.



• What is Data??– Data is the information entered into tables within the

database.

– Something as simple as entering contact details into the‘Contact’ or ‘Address Book’ section of an email clientsuch as Microsoft Outlook, involves entering data into adatabase.

– Data entered can be alphabetical (Names/Addresses) ornumerical (Dates/Currencies)

– Access can store other forms of data such as links towebsites and attachments (Files & Images)

– Data can be entered manually through the keyboard orimported from an external sources



• What is Data Type??– Data types define what type of data will be entered into

each ‘field’ (column) in a table.

– If for example the date ‘12June2007’ or Price 1.59 isentered into a field which by default is set as ‘Text’ theinput characters will remain unchanged.

– If however the data type is set to a currency or date‘Data Type’, numeric data entered will automaticallyformat to the appropriate currency or data/time format.

– Data Types includes Text, Memo, Number, Date/Time,Currency, AutoNumber, Yes/No, OLE Objects orHyperlinks.


• Data Type - Descriptions




• What is a Primary Key??– A primary key is used as a unique identifier for each

record in a database table and is essential when workingwith relational tables.

– To be effective, a primary key cannot have duplicateentries and must be set on a field generating a uniqueidentifier. When creating a new table Accessautomatically adds the ID field with an AutoNumber datatype. By default the ID field is also the primary key.



• What is a Table Index??– A database index is a data structure that improves the

speed of data retrieval operations on a database table atthe cost of slower writes and increased storage space.

– Indexes can be defined using one or more columns of adatabase table.



• What is a Table Relationship??– Table relationships enable you to prevent redundant

data from being store in a table.

– For example, if you are designing a database that willtrack information about books, you might have a tablecalled Titles that stores information about each book,such as the book’s title, date of publication, andpublisher. There is also information you might want tostore about the publisher, such as the publisher's phonenumber, address, and zip code. If you were to store allof this information in the titles table, the publisher’sphone number would be duplicated for each title that thepublisher prints.



• Table Relationship - Types– One-To-Many Relationships

v In a one-to-many relationship, each row in the related to table can berelated to many rows in the relating table. This allows frequently usedinformation to be saved only once in a table and referenced manytimes in all other tables.

v For example, all the customers belonging to a business is stored in acustomer table while all the customer orders are stored in an ordertable. Each customer can have many orders but each order can onlybe generated for a single customer.

v A One-to-Many relationship is created if only one of the relatedcolumns is a primary key or has a unique constraint.



• Table Relationship - Types– Many-To-Many Relationships

v In a many-to-many relationship, one or more rows in a table can berelated to 0, 1 or many rows in another table. In a many-to-manyrelationship between Table A and Table B, each row in Table A islinked to 0, 1 or many rows in Table B and vice versa. A 3rd tablecalled a mapping table is required in order to implement such arelationship.

v For example, classes can be registered by many student, and eachstudent can register in many classes. Student information is storedin a student table, while class information is stored in class table. weneed to create a third table (known as a junction table).



• Table Relationship - Types– One-To-One Relationships

v In a one-to-one relationship, each row in one database table islinked to 1 and only 1 other row in another table. In a one-to-onerelationship between Table A and Table B, each row in Table A islinked to another row in Table B. The number of rows in Table Amust equal the number of rows in Table B.

v For example, products and products details, each products canhave one and only one product detail.


• Access 2016 Objects

PART #1 ACCESS 2016 DATABASE OBJECTS


PART #1: ACCESS 2016 TABLES & RELATIONS

part #1 access 2016 database conceptsis.cba.edu.kw/240/access exercise/database introduction power...

Documents