Database relational database Database management system Access

1
Databaserelational databaseDatabase management
systemAccess

• Sen Zhang

2
What is a databse?

• A database is a collection of information stored
  on a computer in a systematic and structured way,
  which should facilitate information retrieval.

3
What is database management system?

• The tool that can process database is called
  database management system ( not a hammer, an
  integrated software)

4
Relational database and relational database
management system

• Relational database is the database where data
  are structured as relations (tables).
• Correspondingly, the database management system
  is called RDBMS.

5

• Database application cares about not only keeping
  track of information, but also retrieving
  information.
• So, to facilitate information retrieval, a
  database application has to consider how to save
  data in some well defined structures.

6
Why structured?

• Think about how to put your books on shelves.
• Fast retrieval
• But slow down storing
• It is worth of the effort

7

• Database application is meaningful when you need
  to save data and dynamically search data as well
  as derive new data, more powerful than Excel.
• It is especially useful when the application
  concerns about huge amount of information.

8

• One example, all transactions of Walmart.
• How does Walmart maintain all customer
  transactions?
• How does walmart maintain all information about
  different kinds merchandise?
• Why maintain database?
• Why you maintain your house?
• Do you need them later? If yes, then you need.
• You can find out other examples

9

• Excel organizes things in worksheets.
• An Excel Worksheet looks like a huge table.
• However, in the stricter sense, a Worksheet is
  not a table. Why?
• From format point of view, it looks like a table
• From structure point of view, it is not treated
  like a table.

10
Is Excel the right tool for database?

• To certain extend, an Excel workbook can be
  treated as a very rudimentary database, but
• A workbook is
• structured at visualization level and data
  structure level
• but unstructured at semantic level.
• Each cell is independent from the cells in the
  same column.
• Therefore is not appropriate for relational
  database, not the right data.
• And Excel has not designed as a tool to process
  database,
• So, it is not a right tool.

11
Access is the right tool

• In Microsoft office suite, It is Access that is
  the tool to process database stored in a kind of
  specially defined data file.
• It requires data to be specially organized.

12

• Excel does not impose rigid structure
  requirements to data in individual cells.
• Excel is not good at handling massive amounts of
  data.
• Excel does not support queries from different
  tables (actually, tables in Excel are different
  from the Accesss tables!)

13

• It is the Access where tables are used in the
  real sense (It might sound difficult to
  understand at this moment, but it will be clear
  at the end of the lecture) .
• We will see the differences.

14
Why Access is needed?

• How do you maintain information of 5000 students,
  500 different classes and class enrollment for
  Oneonta college?

15
Can you use Excel Worksheets?

• You cannot simply use Excel to accomplish the
  goal for many reasons
• different offices may keep different Excel
  Workbooks for different purposes.
• redundant information will be stored, which
  easily causes inconsistencies across the campus.
• the information changes all the time,
• and a large number of different reports might be
  requested by different people.
• It is tedious and actually impossible to create
  various excel sheets to maintain different
  information separately.
• .

16
Solution is to use database

• We need a better way, a centralized way to store
  data, organize data, retrieve data, and display
  data.
• A database is usually a collection of
  centralized and organized data (or tables).

17

• Database is managed by database management
  system.
• Microsoft Access is a powerful program to create
  and manage your databases.

18

• Access is a database management system, and more
  precisely, a relational database management
  system.
• In a relational database, everything will be
  organized into tables. (a table a relation).

19
What is Microsoft Access?

• It has many built in features to assist you in
  constructing and viewing your information.
• Access is much more involved and is a more
  genuine database application than other programs
  such as
• Microsoft Word(good at documentation),
• Excel (simple spreadsheet good at small amount of
  data.)
• PowerPoint (presentation)
• FrontPage (website development).

20
Define some key Access terminology Field vs.
Field value.

• Field A single characteristic or attribute of a
  person, place, object, event, or idea.
• Field value The specific value, or content, of
  a field is called the field value.

21
Record and table

• Record A set of related field values.
• a Customer may have name, age, birthday, SSN etc.
  
• Table A collection of records that identify a
  category of data, such as Customers, Orders, or
  Inventory.

22
Illustration of fields, recordsand a table
23
A Primary key

• A primary key is a field, or a collection of
  fields, whose values uniquely identify each
  record in a table.
• The primary key uniquely identifies a record in
  the table. In other words, a primary key allows
  user to distinguish one record from another in
  the same table.
• In Access, a table usually has a field, or a
  collection of fields to be designated as the
  primary key. (Not a must, but usually we do.)

24
Relational database and keys

• A relational database is a collection of tables
  that are related to one another based on a common
  field.
• When the primary key of one table is represented
  in a second table to form a relationship, it is
  called a foreign key.

25
Relating tables using a common field
26
(No Transcript)
27
Relational database?

• Every piece of information will be stored in
  relations, i.e. tables.
• For example, student table looks like

Student id name year status
A23232 john 1988 good
A23355 john 1976 good
A34333 kathy 1979 good
28
Relational databse?
Course table
Course id Course name instructor credit
c900 computing sen 3
c905 database sen 3
29
Relational databse?
Enrollment table
sequence Studentid courseid status
1 a23232 c900 n
2 a34333 c905 w
30
Relational database?
Student id name year status
A23232 john 1988 good
A23355 john 1976 good
A34333 kathy 1979 good
Course id Couse name instructor credit
c900 computing sen 3
c905 database sen 3
sequence studentid courseid status
1 a23232 c900 n
2 a34333 c905 w
31
Several Terminologies

• A database is a collection of related
  information.
• An object is a member in the database such as a
  table, query, form, or macro.
• A table is a grouping of related data organized
  in fields (columns) and records (rows) on a
  datasheet. By using a common field in two tables,
  the data can be combined. Many tables can be
  stored in a single database.
• A field is a column on a datasheet and defines a
  data type for a set of values in a table. For a
  mailing list table might include fields for first
  name, last name, address, city, state, zip code,
  and telephone number.
• A record in a row on a datasheet and is a set of
  values defined by fields. In a mailing list
  table, each record would contain the data for one
  person as specified by the intersecting fields.
• Design View provides the tools for creating
  fields in a table.
• Datasheet View allows you to update, edit, and
  delete in formation from a table.

32
More detailed Breakdown of tables Hierarchy that
Microsoft Access uses

• TableA table is a collection of data about a
  specific topic. There can be multiple tables in a
  database. Example 1) Students Example 2)
  enrollments
• FieldFields are the different categories within
  a Table. Tables usually contain multiple fields.
  Example 1) Student LastName Example 2)
  Student FirstName
• DatatypesDatatypes are the properties of each
  field. A field only has 1 datatype. FieldName)
  Student LastName Datatype) Text
• Values for example a student lastname could be
  herbert.

33
Blank Access database

• Unlike Word documents, Excel worksheets, and
  Power Point presentations, you must save an
  Access database before you start working on it.
  After selecting "Blank Access database", you will
  first be prompted to specify a location and a
  name for the database.

34
(No Transcript)
35
Database Window

• The Database Window organizes all of the objects
  in the database. The default tables listing
  provides links for creating tables and will list
  all of the tables in the database when they have
  been added.

36
(No Transcript)
37
Design View

• Design View customizes the fields in the database
  so that data can be entered.

38
(No Transcript)
39
Datasheet View

• The datasheet allows you to enter data into the
  database

40
(No Transcript)
41
Access database wizards, pages, and projects

• Access' wizards and layout are existing database
  structures that only need data input. Select a
  database type and click OK. Name the database on
  the next screen.

42
(No Transcript)
43
Forms

• A form is nothing more than a graphical
  representation of a table. You can add, update,
  delete records in your table by using a form.
• NOTE Although a form can be named different from
  a table, they both still manipulate the same
  information and the same exact data. Hence, if
  you change a record in a form, it will be changed
  in the table also.
• Actually, a form and a datasheet are simply
  different views of the same table.

44
Form view vs. Datasheet view

• A form is very good to use when you have numerous
  fields in a table. This way you can see all the
  fields in one screen, whereas if you were in the
  table view (datasheet) you would have to keep
  scrolling horizontally to get the field you
  desire.
• A form is also very good to use when you have
  numerous records in table. This way you can
  concentrate on single record in one screen,
  whereas if you were in the table view (datasheet)
  you would have to keep scrolling vertically to
  get the record you desire.

45
What is an Access query?

• If you want to see just a portion of the data in
  a table you can create a query.
• A query is a question you ask about the data
  stored in a database table.
• Access responds by displaying the data according
  to your question.
• For example, if you ask to see all the customers
  from New York, the response would be to display
  only the records whose state field matches with NY

46
Open an existing query and create new queries

• You can open an existing query by clicking
  Queries on the Objects bar and then selecting the
  query you want to open.
• You can also create your own queries by clicking
  New on the Database window.
• To create a new query, you can use the Simple
  Query Wizard, which will bring you through the
  selections you want for your query.

47
Navigating a query and sorting the results

• When you run and get the results of your query,
  you can reorganize the data by sorting the
  datasheet in either ascending or descending
  order.
• Click the pointer anywhere in the column you wish
  to sort
• Click the Sort Ascending or Sort Descending
  buttons on the Query Datasheet toolbar to sort
  the results in the desired sequence
• You can navigate through the records by using the
  navigation buttons on the Navigations toolbar.

48
Reports

• A report is an effective way to present your data
  in a printed format. Because you have control
  over the size and appearance of everything on a
  report, you can display the information the way
  you want to see it.