At the beginning of the lesson Dr Noorhidayawati earlier explain what is database management system. I get more information and gain knowledge on database management system.The purpose of database, how to store the information, how to arrange file quickly and how to produce database electronically.Relational databases and text retrieval is categories in many, for this subject we focus on MS Access and Winisis.From this lesson, i have learn an new experience to create databases using MS access. There is many features in MS database to create a great database.The first experience using MS access is in the class on 18 january 2011. Dr Noorhidayawati explain and guide us to do learn MS access and gave us an assessment to be done using MS Access. We were guided to create table, report,and use all the functionality in MS access. The assignment is about an Landskap project, referred to notes provided, i manage to finish up my assignments They difficulties that i face is when create report for late due.It took me two days to complete and submit thru spectrum.
In MS access we can manage, organize , store and manipulate data which created in database.We can create database quickly ,accurately and usage of wizards and templates make things easy to use MS access. The relationship of the database can be one to one, one to many and many to many. I'm my working environment , i always use MS excel to generate reports.After learn t MS Access , i understand the different between both.Excel is one to one relationship only.
Microsoft Office Access Overview
Microsoft Office Access, previously known as Microsoft Access, is a relational database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of applications, included in the Professional and higher editions or sold separately. In mid-May 2010, the current version of Microsoft Access 2010 was released by Microsoft in Office 2010; Microsoft Office Access 2007 was the prior version.
Access stores data in its own format based on the Access Jet Database Engine. It can also import or link directly to data stored in other applications and databases. Software developers and data architects can use Microsoft Access to develop application software, and "power users" can use it to build simple applications. Like other Office applications, Access is supported by Visual Basic for Applications, an object-oriented programming language that can reference a variety of objects including DAO (Data Access Objects), ActiveX Data Objects, and many other ActiveX components. Visual objects used in forms and reports expose their methods and properties in the VBA programming environment, and VBA code modules may declare and call Windows operating-system functions.
What is a database?
A database is a tool for collecting and organizing information. Databases can store information about people, products, orders, or anything else. Many database start as a list in a word-processing program or spreadsheet. As the list grows bigger, redundancies and inconsistencies begin to appear in the data. The data becomes hard to understand in list form, and there are limited ways of searching or pulling subsets of data out for review. Once these problems start to appear, it's a good idea to transfer the data to a database created by a database management system (DBMS), such as Office Access 2007.
A computerized database is a container of objects. One database can contain more than one table. For example, an inventory tracking system that uses three tables is not three databases, but one database that contains three tables. Unless it has been specifically designed to use data or code from another source, an Access database stores its tables in a single file, along with other objects, such as forms, reports, macros, and modules. Databases created in the Access 2007 format have the file extension .accdb, and databases created in earlier Access formats have the file extension .mdb. You can use Access 2007 to create files in earlier file formats (for example, Access 2000 and Access 2002-2003).
Using Access, you can:
- Add new data to a database, such as a new item in an inventory
- Edit existing data in the database, such as changing the current location of an item
- Delete information, perhaps if an item is sold or discarded
- Organize and view the data in different ways
- Share the data with others via reports, e-mail messages, an intranet , or the Internet
The parts of an Access database
The following sections are short descriptions of the parts of a typical Access database.
A database table is similar in appearance to a spreadsheet, in that data is stored in rows and columns. As a result, it is usually quite easy to import a spreadsheet into a database table. The main difference between storing your data in a spreadsheet and storing it in a database is in how the data is organized.
To get the most flexibility out of a database, the data needs to be organized into tables so that redundancies don't occur. For example, if you're storing information about employees, each employee should only need to be entered once in a table that is set up just to hold employee data. Data about products will be stored in its own table, and data about branch offices will be stored in another table. This process is called normalization.
Each row in a table is referred to as a record. Records are where the individual pieces of information are stored. Each record consists of one or more fields. Fields correspond to the columns in the table. For example, you might have a table named "Employees" where each record (row) contains information about a different employee, and each field (column) contains a different type of information, such as first name, last name, address, and so on. Fields must be designated as a certain data type, whether it's text, date or time, number, or some other type.
Another way to describe records and fields is to visualize a library's old-style card catalog. Each card in the cabinet corresponds to a record in the database. Each piece of information on an individual card (author, title, and so on) corresponds to a field in the database.
Forms are sometimes referred to as "data entry screens." They are the interfaces you use to work with your data, and they often contain command buttons that perform various commands. You can create a database without using forms by simply editing your data in the table datasheets. However, most database users prefer to use forms for viewing, entering, and editing data in the tables.
Forms provide an easy-to-use format for working with the data, and you can also add functional elements, such as command buttons, to them. You can program the buttons to determine which data appears on the form, open other forms or reports, or perform a variety of other tasks. For example, you might have a form named "Customer Form" in which you work with customer data. The customer form might have a button which opens an order form where you can enter a new order for that customer.
Forms also allow you to control how other users interact with the data in the database. For example, you can create a form that shows only certain fields and allows only certain operations to be performed. This helps protect data and to ensure that the data is entered properly.
Reports are what you use to summarize and present data in the tables. A report usually answers a specific question, such as "How much money did we receive from each customer this year?" or "What cities are our customers located in?" Each report can be formatted to present the information in the most readable way possible.
A report can be run at any time, and will always reflect the current data in the database. Reports are generally formatted to be printed out, but they can also be viewed on the screen, exported to another program, or sent as e-mail message.
Queries are the real workhorses in a database, and can perform many different functions. Their most common function is to retrieve specific data from the tables. The data you want to see is usually spread across several tables, and queries allow you to view it in a single datasheet. Also, since you usually don't want to see all the records at once, queries let you add criteria to "filter" the data down to just the records you want. Queries often serve as the record source for forms and reports.
Certain queries are "updateable," meaning you can edit the data in the underlying tables via the query datasheet. If you are working in an updateable query, remember that your changes are actually being made in the tables, not just in the query datasheet.
Queries come in two basic varieties: select queries and action queries. A select query simply retrieves the data and makes it available for use. You can view the results of the query on the screen, print it out, or copy it to the clipboard. Or, you can use the output of the query as the record source for a form or report.
An action query, as the name implies, performs a task with the data. Action queries can be used to create new tables, add data to existing tables, update data, or delete data.
Macros in Access can be thought of as a simplified programming language which you can use to add functionality to your database. For example, you can attach a macro to a command button on a form so that the macro runs whenever the button is clicked. Macros contain actions that perform tasks, such as opening a report, running a query, or closing the database. Most database operations that you do manually can be automated by using macros, so they can be great time-saving devices.
Modules, like macros, are objects you can use to add functionality to your database. Whereas you create macros in Access by choosing from a list of macro actions, you write modules in the Visual Basic for Applications (VBA) (Visual Basic for Applications (VBA): A macro-language version of Microsoft Visual Basic that is used to program Microsoft Windows-based applications and is included with several Microsoft programs.) Programming language. A module is a collection of declarations, statements, and procedures that are stored together as a unit. A module can be either a class module or a standard module. Class modules are attached to forms or reports, and usually contain procedures that are specific to the form or report they're attached to. Standard modules contain general procedures that aren't associated with any other object. Standard modules are listed under Modules in the Navigation Pane, whereas class modules are not.
Screen Shot of features of MS Access :
Assignment Question :
Assignment 1: Creating and Designing a Database Using Microsoft Access (10%)
This assignment involves using Microsoft Access to create a database for your library resources. Your completed file called Landscapes.mdb will contain at least 3 tables, 2 queries, 2 forms and 1 report.
Plan your database on paper first. This will take quite a bit of time, so it’s good to go ahead and get started. As you plan your database consider the cardinality of the relationship between the various elements: one-to-one, one-to-many or many-to-many. Create the table structures required, specifying the field name, data type, description, validation rules (if any), default values (if any), and primary key for each table. You will need at least three tables for this assignment. You may use a wizard to create the tables if you wish. Create forms to input data for your tables, using wizard if you choose. You can include graphics/images on the forms if you wish. Add necessary records to each table. Be sure that you have data that will allow you to create at least one report stated below. You may create additional reports if you wish.
You may also create a Switchboard that opens automatically when the database is opened. The Switchboard should allow you to open your forms and reports. For extra credit you can create a Switchboard that allows you to enter data into the forms.
Question
Mr. Ramly works for his own landscape architecture firm Sari Tuah Landscapes which specializes in landscape designs for residential and commercial customers and numerous public agency. The company provides a wide range of services from site analyses and feasibility studies, to drafting and administrering construction documents for projects of various scales. Mr. Ramly and his staff depend on computers to help manage all aspects of the firm’s operations, including financial and information management. Several months ago the company upgraded to Microsoft Windows and Microsoft Access 2007, a DBMS used to enter, maintain and retrieve related data in a format known as a databse. Mr. Ramly and his staff want to use Access to maintain such data as information about customers, contracts and invoices. He asks for your help in creating the necessary Access database.
A. Create the following three tables – all fields must be of correct type and suitable size. The required field property should be used appropriately. You are required to set the primary key for each table and establish correct relationships between the tables.
1. CUSTOMER
a. To store data about each customer
b. To have 10 text fields: Customer ID, Company, Last Name, First Name, Phone, Address, City, State, Postcode, Email
c. Indexes are to be created on the fields: Customer ID, Company, Last Name, First Name, City
a. Customer ID is a unique field, size 5
2. CONTRACTS
b. To store data about a customer’s contract.
c. To have 5 text fields: Contract Number, Customer ID, Contract Amount, Signing Date, Contract Type
d. Contract Number is a unique field, size 4
e. Signing Date is a date field, Format = mm/dd/yyyy
3. INVOICE
a. To store data about a customer’s invoice.
b. To have 5 text fields: Invoice Number, Contract Number, Invoice Amount, Invoice Item (such as schematic planning, planting, construction observation, etc) , Invoice Date, Invoice Paid
c. Invoice Number is a unique field, size 4
d. Invoice Amount is a Currency field, Format=currency Decimal Places=2
e. Invoice Date is a date field, Format = mm/dd/yyyy
f. Invoice Paid is a Yes/No field to indicate whether the customer has paid the invoice or not, default to ‘No’.
B. Create two forms for the CUSTOMER and the CONTRACTS tables respectively. Enter 10 records. Use Customer ID from 10001 to 10020
C. Enter 10 records for the CONTRACTS table which fullfill the following requirements:
a. One of the customer is YOU, indicate that you have 4 contracts.
b. The other 6 customers are your WXGB6105 course-mates.
c. Two of your course-mates has 2 contracts and 3 invoices
d. Another two of your course-mates have Unpaid Invoces with Late Fees. Late Fees is calculated by multiplying 0.03 late fees by invoice amount
e. The remaining course-mates have paid their invoices.
D. Create a one page report called “Unpaid Invoces with Late Fees” based on a query “Late Fees for Unpaid Invoices”. The report is to be grouped and sorted by Company and is to show for each Customer:
a. The first name, last name and phone
b. The contract Number, Contract Amount,
c. The Invoice Item, Invoice Amount and Late Fees
To be handed - via Spectrum: Your landscape database (.mdb file), containing the tables, forms, query as well as other additional features such as switchboard , relational diagram – clearly marked with your name and matrix number.
No comments:
Post a Comment