Popular in Course
verified elite notetaker
Popular in Department
This 11 page Study Guide was uploaded by Usman Qureshi on Wednesday November 18, 2015. The Study Guide belongs to a course at a university taught by a professor in Fall. Since its upload, it has received 29 views.
Reviews for IDB_Lab_01
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 11/18/15
CS3020-Introduction to Database System Lab – 1 Introduction, Introduction to MS Access elcome to “Introduction to Database System” Lab. In this lab we will try W to be familiar with some definitions from the domain of Database and MS-Access. A database is a structured collection of records or data that is stored in a computer so that a computer program can consult it to answer queries. The records retrieved in answer to queries are information that can be used to make decisions. The computer program used to manage and query a database is known as a database management system (DBMS). A relational database is a database that conforms to the relational model, and could also be defined as a set of relations or a database built in an RDBM. Software created to manage generalized databases is usually called a database management system or DBMS. Several software architectures are possible: For smaller single user databases often all functions are managed by one program. In larger and multiple user databases usually a number of programs are involved and most commonly a client-server architecture is adopted. The DBMS front-end (i.e., the clients) is concerned mainly with data entry, enquiry and reporting. The back-end (i.e. the server) is the set of programs that actually control data storage, responding to requests from the frontend. Searching and sorting is usually performed by the server. There are a wide variety of database implementations, from simple tables stored in a single file each to very large databases with many millions of records, stored in rooms full of disk drives or other peripheral electronic storage devices. Databases are used in many applications, spanning virtually the entire range of computer software. Databases are the preferred method of storage for large multiuser applications, where coordination between many users is needed. Even individual users find them convenient, though, and many electronic mail programs and personal organizers are based on standard database technology. In addition to their data model, most practical databases attempt to enforce a database transaction model that has desirable data integrity properties. Ideally, the database software should enforce the ACID rules, summarized here: Atomicity – database modifications must follow an “all or nothing” rule; either all or no operations are completed. (Transactions that can't be finished must be completely undone). Each transaction is said to be “atomic” if one part of the transaction fails, the entire transaction fails. Example For an online airline-booking system, a booking may consist of 2 separate actions that together form a transaction -- paying for the seat, and reserving the seat for the customer who’s just paid. Business logic dictates that these two, though distinct and separate actions, must occur together. If one happens without the other, problems Page 1 of 11 CS3020-Introduction to Database System Lab – 1 Introduction, Introduction to MS Access can occur. For example, the system may reserve the same seat for two separate customers. It is essential that a database system that claims to offer atomicity be able to do so even in the face of failure in power supply or the underlying operating system or application that uses the database. Consistency - all transactions must leave the database in consistent state, that is, only valid data will be written to the database. If, for some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules. On the other hand, if a transaction successfully executes, it will take the database from one state that is consistent with the rules to another state that is also consistent with the rules. Example A simple rule of consistency may state that the ‘Result’ column of a database may only have the values ‘Pass’ or ‘Fail’. If a user attempts to enter something else, say ‘Withdraw’ then a database consistency rule kicks in and disallows the entry of such a value. Consistency rules can get quite elaborate, for example a bank account number must follow a specific pattern- it must begin with a ‘C’ for checking account or ‘S’ for savings account, then followed by 14 digits that are picked from the date and time, in the format YYYYMMDDHHMISS. Database consistency does not only occur at the single-record level. In our bank example above, another consistency rule may state that the ‘Customer Name’ field cannot be empty when creating a customer. Isolation - transactions can't interfere with each other's work and incomplete work isn't visible to other transactions. Example: If Person1 issues a transaction against a database at the same time that Person2 issues a different transaction, both transactions should operate on the database in an isolated manner. The database should either perform Person1’s entire transaction before executing Person2’s or vice-versa. Note that the isolation property does not ensure which transaction will execute first, merely that they will not interfere with each other. Durability - successful transactions must persist through crashes. It ensures that any transaction committed to the database will not be lost. Durability is ensured through the use of database backups and transaction logs that facilitate the restoration of committed transactions in spite of any subsequent software of hardware failures. Page 2 of 11 CS3020-Introduction to Database System Lab – 1 Introduction, Introduction to MS Access Example There are several organizations, such as banks and hospitals, whose very existence depends on the information systems run on databases. The ability to recover 100% of all committed transactions is absolutely crucial. The recovery rate must be 100 percent, not 90 percent or even 99.6 percent. In addition, this recovery must be permanent, meaning all transactions must be reconstructed, even if the database server crashes due to OS failure or power loss. Collections of data Data may be collected, manipulated and retrieved in various ways: plain text editor - simple editing and retrieval word processor - adds tables and simple calculations spreadsheet program - adds more sophisticated calculations database management system (DBMS) - adds formats, structures, rules, ... Reasons for a DBMS A DBMS is a software package for defining and managing a database. A ‘real’ database includes definitions of o field names o data formats (text? binary? integer? etc.) o record structures (fixed-length? pointers? field order, etc.) o file structures (sequential? indexed? etc.) rules for validating and manipulating data data Determining Data types for a field Each field has a data type. This indicates the type of data that can be stored in the field. Three of the most commonly used data types are: 1. Text — The field can contain any characters. A maximum number of 255 characters is allowed in a field whose data type is Text. 2. Number — The field can contain only numbers. The numbers either can be positive or negative. Fields are assigned this type so they can be used in arithmetic operations. Fields that contain numbers but will not be used for arithmetic operations (such as postal codes) usually are assigned a data type of Text. 3. Currency — The field can contain only monetary data. The values will appear with currency symbols, such as dollar signs, commas, and decimal points, and with two digits following the decimal point. Like numeric Page 3 of 11 CS3020-Introduction to Database System Lab – 1 Introduction, Introduction to MS Access fields, you can use currency fields in arithmetic operations. Access assigns a size to currency fields automatically. If anyone is interested in other datatypes, follow the link for a good online resource about the other datatypes. Data type explanation ODBC Open Database Connectivity (ODBC) is a standard software API for connecting to database management systems (DBMS). This API is independent of any one programming language, database system or operating system. ODBC was created by the SQL Access Group and first released in September, 1992. In addition to the ODBC software, a separate module or driver is needed for each database to be accessed. The functions in the ODBC API are implemented by these DBMS-specific drivers. ODBC allows programs to use SQL requests that will access databases without having to know the proprietary interfaces to the databases. It handles the SQL request and converts it into a request the individual database system understands. Most current DBMSs support ODBC which means that computer programs that rely on ODBC can connect to several different brands of DBMSs using the same basic code. The Microsoft® Open Database Connectivity (ODBC) interface is a C programming language interface that makes it possible for applications to access data from a variety of database management systems (DBMSs). The ODBC interface permits maximum interoperability — an application can access data in diverse DBMSs through a single interface. Furthermore, that application will be independent of any DBMS from which it accesses data. Users of the application can add software components called drivers, which interface between an application and a specific DBMS. Market Share (Usage Share) of DBMSs According to research company Gartner, the five leading commercial relational database vendors by revenue in 2011 were Oracle (48.8%), IBM (20.2%), Microsoft (17.0%), SAP including Sybase(4.6 %), and Teradata (3.7%). The three leading open source implementations are MySQL, PostgreSQL, and SQLite. MariaDB is a prominent fork of MySQL prompted by Oracle's acquisition of MySQL AB. According to Gartner, in 2008, the percentage of database sites using any given technology were (a given site may deploy multiple technologies): Oracle Database Microsoft SQL Server MySQL (Oracle Corporation) IBM DB2 IBM Informix Page 4 of 11 CS3020-Introduction to Database System Lab – 1 Introduction, Introduction to MS Access SAP Sybase Adaptive Server Enterprise SAP Sybase IQ Teradata According to DB-Engines, the most popular systems are Oracle, MySQL, Microsoft SQL Server, PostgreSQL and IBM DB2. MS Access (2007) Microsoft Access is a powerful program to create and manage your databases. 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 Works. Microsoft Access is packaged with Microsoft Office Professional which combines the Jet relational database engine with a graphical interface intended to make it possible for relatively unskilled programmers and non-programmer "power users" to build front ends to databases. For skilled developers and data architects, it can allow for the rapid development of applications. One of the benefits of Access from a programmer's perspective is its relative compatibility with SQL – queries may be viewed and edited as SQL statements. First of all you need to understand how Microsoft Access breaks down a database. Some keywords involved in this process are: Database File, Table, Record, Field, Data-type. Here is the Hierarchy that Microsoft Access uses in breaking down a database. Database File: This is your main file that encompasses the entire database and that is saved to your hard-drive or floppy disk. Example) StudentDatabase.mdb 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) Teachers Field:Fields are the different categories within a Table. Tables usually contain multiple fields. Example #1) Student LastName Example #2) Student FirstName Page 5 of 11 CS3020-Introduction to Database System Lab – 1 Introduction, Introduction to MS Access Datatypes:Datatypes are the properties of each field. A field only has 1 datatype. FieldName) Student LastName Datatype) Text Quick start Microsoft Access Click on ; Start All Programs [Microsoft Office] Microsoft Office Access 2007 Creating a New Database with Access When you launch Access 2007, you will see the Getting Started window. In the left pane, the template categories including the featured local templates are listed, as well as the categories on Office Online. Templates are pre-built databases focused on a specific task that you can download and use immediately. You will also see the New Blank Database option that allows you to build your own database from scratch. Getting Started Window When you choose the New Blank Database option at the top of this window, you will be prompted to rename the database from the default name, which Page 6 of 11 CS3020-Introduction to Database System Lab – 1 Introduction, Introduction to MS Access is Database1.accdb. Rename the database whatever you would like the name to be. In the example below, we named the database Ready2Read since that is the name of the store in our scenario. Create a New Blank Database Click Create to finish naming the database. Setting up Tables The new database opens with one table showing as a default. It also defaults to naming this table Table1 in both the Navigation Pane, and the Table tab itself. You will want to name your tables based on your database design plan. Page 7 of 11 CS3020-Introduction to Database System Lab – 1 Introduction, Introduction to MS Access Default Table Name Naming a Table To give the table a unique name, you must first click on the Microsoft Office Button in the upper left hand corner of the application. Naming Table1 Then, select Save from the menu. The Save As dialog box will appear to let you save the table as whatever name you choose. Save As Dialog Box The new table names appear in both the Navigation Pane and the Table tab itself, as you can see in the picture below. Page 8 of 11 CS3020-Introduction to Database System Lab – 1 Introduction, Introduction to MS Access New Table Name TIP: Give your tables logical, easy to understand names. To Rename a Table With the table closed, right click on the Table you wish to rename in the Navigation Pane. Select the Renameoption that appears in the menu. Rename Table from Navigation Pane The table name will be highlighted and a cursor will appear, which means you can now type in the new name right there. Left click anywhere outside of the Table name to make the change. To Close a Table There are several ways to close an active table. You can right click on the Table tab and choose Close from the menu. Page 9 of 11 CS3020-Introduction to Database System Lab – 1 Introduction, Introduction to MS Access Close Table from Table Tab A more common method is to click the X that appears in the upper right hand corner of the active database object window. Close Table from Document Window To Open a Table To open a table, right click the Table name of the table you wish to open in the Navigation Pane. Then, choose Open from the menu. Open Table from Navigation Pane A more common method is to double click the table name in the Navigation Pane. The selected table will open in the active database object window. Adding More Tables to the Database By default, Access 2007 starts out with one table. To add more tables to the database, click on the Create tab on the Ribbon. Page 10 of 11 CS3020-Introduction to Database System Lab – 1 Introduction, Introduction to MS Access Lab Task: Create following tables and insert at least five records in each table; Students (cms_no, sname, date_of_birth, contact_no, cgpa) Courses ( define you own fields ) Employees ( define you own fields ) Assignment: Task 1: Research some of the DBMSs i.e. Oracle, MySQL, Microsoft SQL Server, MS Access, PostgreSQL and IBM DB2 and write a report on them. Some hints on what to write is to write about the manufacturer, year of release, what kind of data it handles and its limitations, and their market share. Instructions on submission: - You are required to submit this assignment in the first 10 minutes of the lecture in next class (insha-Allah). Afterwards it would be considered as late. - The assignment report you are going to submit should be hand-written and should not be more than 5 pages in total. However, you can write less than that if the text fully satisfies the requirement of the task. Reference(s): i) http://www.bcschools.net/staff/AccessHelp.htm ii) Wikipedia, the free encyclo(http://en.wikipedia.org/wiki/Main_Page) iii) https://www.techopedia.com/definition/24729/atomicity iv) http://en.tekstenuitleg.net/articles/software/access-data-types Lab Instructor: Uzair Muhammad, Sikandar Azam Shah, Riphah International University email: email@example.com, firstname.lastname@example.org Page 11 of 11
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'