Database relational database Database management system Access by coronanlime

VIEWS: 92 PAGES: 48

									           Database
     relational database
Database management system
            Access
          Sen Zhang
         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.
  What is database management
             system?
• The tool that can process database is
  called database management system ( not
  a hammer, an integrated software)
 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.
• 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.
            Why structured?
• Think about how to put your books on
  shelves.

• Fast retrieval
• But slow down storing
• It is worth of the effort
• 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.
• 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
• 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.
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.
     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.
• 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 Access’s tables!)
• 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.
     Why Access is needed?
• How do you maintain information of 5000
  students, 500 different classes and class
  enrollment for Oneonta college?
  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.
  – ….
   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).
• Database is managed by database
  management system.
• Microsoft Access is a powerful program to
  create and manage your databases.
• 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).
       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).
       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.
           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.
Illustration of fields, records
          and a table
             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.)
  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.
Relating tables using a common
              field
           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
        Relational databse?

Course table



Course id      Course      instructor   credit
               name
c900           computing   sen          3

c905           database    sen          3
             Relational databse?

    Enrollment table


sequence               Studentid   courseid   status


1                      a23232      c900       n


2                      a34333      c905       w
             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
           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.
    More detailed Breakdown of tables
     Hierarchy that Microsoft Access
                  uses
•    Table:A table is a collection of data about a specific topic. There
    can be multiple tables in a database.
    Example #1) Students
    Example #2) enrollments

• Field:Fields are the different categories within a Table. Tables
  usually contain multiple fields.
  Example #1) Student LastName
  Example #2) Student FirstName

• Datatypes:Datatypes 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.
      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.
           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.
              Design View

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

• The datasheet allows you to enter data
  into the database
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.
                       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.
 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.
    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
      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.
      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.
                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.

								
To top