Create database objects in web development and database administration .pptx

Operate database application prepared ByTsedale.B

 MODULETITLE: Operate Database Application  MODULE DESCRIPTION: - This module defines the competency required to operate database applications and perform basic operations.  LEARNING OUTCOMES (OBJECTIVES)  At the end of the module the learner must be able to:  LO1. Create database objects.  LO2. Customize basic settings  LO3. Create forms  LO4. Create reports  LO5. Retrieve information prepared ByTsedale.B

prepared ByTsedale.B This learning guide was developed to provide you the necessary information regarding the following Basic Design Principles Opening And Designing Database Application Database Object Creating Database Object Modifying Database Object Creating Relationship Adding, ModifyingAnd Deleting Records SavingAnd Compiling Database Objects

prepared ByTsedale.B This guide will also assist you& you will be able to  Know Basic Design Principles  OpeningAnd Designing Database Application  Database Object  Modify Database Object  Create Relationship  Add, ModifyAnd Delete Records  Save And Compile Database Objects

prepared ByTsedale.B LO1. Create database objects. Introduction to Database  A database can best be described as a way of storing large amounts of information.  The data can be retrieved and we can even ask questions of the data and get answers.  For example:You may want to know how many Students enrolled in every occupational level.  MSAccess (MS Office Access 2007) is a database management tool that enables one to store relevant data.  This also has the capabilities to retrieve, sort, summarize report and result immediately and effectively.

prepared ByTsedale.B Cont…  It can combine data from various files (tables) through creating relationships and can make data entry more efficient and accurate through the use of forms.  MicrosoftAccess (MS Access) enables to manage all important information from a single database file.  Within the file, can use the different objects/items:

prepared ByTsedale.B Cont… What is a database?  refers to a set of related data and the way it is organized. Access to this data is usually provided by a "database management system" (DBMS) consisting of an integrated set of computer software that allows users to interact with one or more databases and provides access to all of the data contained in the database

prepared ByTsedale.B Con… DBMSs provide various functions that allow management of a database and its data which can be classified into four main functional groups:  Data definition – Creation, modification and removal of definitions that define the organization of the data.  Update – Insertion, modification, and deletion of the actual data.  Retrieval – Providing information in a form directly usable or for further processing by other applications. The retrieved data may be made available in a form basically the same as it is stored in the database or in a new form obtained by altering or combining existing data from the database.  Administration – Registering and monitoring users, enforcing data security, monitoring performance, maintaining data integrity, dealing with concurrency control, and recovering information that has been corrupted by some event such as an unexpected system failure

prepared ByTsedale.B Why use databases? Some reasons:  To organize information  To be able to get reports from data  To protect data – the security features of a database allow you to specify who has access, what data they can see and what they can do with the data  To be able to share data

prepared ByTsedale.B Some Common uses of Databases  In a university, there is a database  Containing information about yourself, the course you are enrolled in, the dormitory you have been given..  Containing details of Staff who work at the university at personnel, payroll, etc.  When you visit your library  There may be a database containing details of the books in the library and details of the users,  The database system handles activities such as

prepared ByTsedale.B Con…  Allowing a user to reserve a book  Charging materials to users  Notifying when materials are overdue : Sends out reminders to borrowers who have failed to return books on the due data  The system will have a bar code reader to keep track of books and users.  In travel agencies  When you make inquiries about a travel, the travel agent may access databases containing flight details  Flight no., date, time of departure, time of arrival  Which passenger is in which flight??

prepared ByTsedale.B Con…  Insurance  When you wish to take out insurance, there is database containing  Your personal details: name, address, age  information on whether you drink or smoke,  Your medical records to determine the cost of the insurance  Supermarkets  When you buy goods from some supermarkets, a database will be accessed…..  The checkout assistant will run a bar code reader over the purchases – It is linked to a database application program, which uses the bar code to find out the price of the item from a products database. It also reduces the number of such items in stock.  If reorder level falls below the threshold, the system may automatically place an order to obtain more stocks.

prepared ByTsedale.B Characteristics of data in the database  Data in the database are shared by different user and application.  Data in the database are permanent  Data should be secured and protected from unauthorized users.  Whenever more than one data elements in a database represent related data the data values should be consisted.

prepared ByTsedale.B Con…  Data should not be duplicated in the database.  Data should be independent in the database.  Data should be well organized.  It should be flexible to change.  Data should be correct.  Data should be available when needed.

prepared ByTsedale.B The Database Design Principles Usability:Any information which we are storing in any organization should be meaningful for that organization. If we are storing those factors which are actually not fit withorganization’s requirement then this is just waste of resources. Extensibility:As we know that everyday new business requirements come up and every day there is a need to change or enhance information system to capture new requirements. Data Integrity: Now at this point we understand that information is very much important for any organization. Based on the historic information, every organization makes different strategies, decisions for growth. Is the overall accuracy, completeness, and consistency of data.

prepared ByTsedale.B cont...  Entity Integrity: Involves the structure (primary key and its attributes) of the entity. If the primary key is unique and all attributes are scalar and fully dependent on the primary key, then the integrity of the entity is good. In the physical schema, the table’s primary key enforces entity integrity.  Domain Integrity: It defines that data should be of correct type and we should handle optional data in correct way.We should apply Nullability to those attributes which are optional for organization.We can define proper data types for different attributes based on organization’s requirement so that correct format data should present in system.

prepared ByTsedale.B cont...  Referential Integrity:This defines if any entity is dependent on another one then parent entity should be there in the system and should be uniquely identifiable.We can do this by implementing foreign keys.  User defined integrity:There are few business rules which we cannot validate just by primary keys, foreign keys etc.  User-defined integrity User-defined integrity involves the rules and constraints created by the user to fit their particular needs. Sometimes entity, referential, and domain integrity aren't enough to safeguard data.  Performance:As we know that information should be readily available as requested. some time there will be impact on performance if database design is poor or we’ll not take any actions to improve performance.

prepared ByTsedale.B cont...  Availability:The availability of information refers to the information’s accessibility when required regarding uptime, locations, and the availability of the data for future analysis. Disaster recovery, redundancy, archiving, and network delivery all affect availability.  Security: For any organizational asset, the level of security must be secured depending on its value and sensitivity. Sometime organizations has suffered a lot because of data leaks which results in loss of faith and tends to business risk. So security is one of the most important aspect of good database design.

prepared ByTsedale.B Cont…  Refine your design.Analyze your design for errors. Create the tables and add a few records of sample data. See if you can get the results you want from your tables. Make adjustments to the design, as needed  Apply the normalization rules.Apply the data normalization rules to see if your tables are structured correctly. Make adjustments to the tables, as needed.

prepared ByTsedale.B Database Objects  A database object is any defined object in a database that is used to store or reference data.  Database objects are components that save and query information. A database contains objects that are used to store and display large, relational data.  Database Tables: - Recently, we define it as a file but technically, it was defined as a container or a worksheet-like container where the collection of data has been stored.

prepared ByTsedale.B Introduction to Microsoft Access 2007  Microsoft Access is a computerized Database Management System (DBMS) that allows efficient data storage and retrieval as well as efficient and effective data presentation. You can create a database where information can be stored in an organized manner which will allow tailoring to efficient, effective data retrieval and presentation.

prepared ByTsedale.B Con…  A database is stored information that is related to a particular topic or purpose. A typical example of a database is a file cabinet where you can store diverse information in different drawers.Another example of a database is an address book in which you can organize desired information such as last name, first name, home address, phone number, cell number, email address et cetera that can be organized by last name in alphabetical order.

prepared ByTsedale.B Con…  The difference between a paper or document-based database and Access is the efficiency and effectiveness that a computerized database provides. Users are able to store and retrieve information fast and able to present information effectively using Access. Most of all, it is cost- effective because large amounts of information can be maintained in a personal computer for an indefinite period of time.

prepared ByTsedale.B Data types in Microsoft Access The different kinds of MsAccess2007 data types are: • Text: allows for the storage of any kind of data, characters, digits and special characters • Memo: is used for texts of more than 255 characters such as comments or explanations. • Number: for numerical data used in mathematical calculations. • Date/Time: for the introduction of date and time from the year 100 to 9999. • Currency: For monetary/economic values and numerical data used in mathematical calculations in which the data involved contains between one and four decimals. • Autonumber: a unique sequential number (increasing one by one), or a number that Access assigns every time it adds a new record to a table. • Yes/No:Yes and No values, and fields that contain one of two values (Yes/No, True/False orActivated/Deactivated).

prepared ByTsedale.B Cont…  OLE Object: an object such as a Microsoft Excel spreadsheet, a MicrosoftWord document, graphics, images, sounds, or other binaries. Used to embed or link to documents from other programs like Excel andWord.  Hyperlink: text or a combination of text and numbers stored as text and used as a hyperlink address.  Attachment: Used to store files in anAccess database.The attachment data type lets you store one or more files per record.  Lookup wizard…:A lookup wizard field lets the user choose from a predefined set of options, like a "male" or "female" selection or a "country" selection.

prepared ByTsedale.B Naming conventions Why use a naming convention? A naming convention should be used in the process of working with databases because of several reasons:  It provides a clear structure, each component using the standard naming rules.  The database logic can be understood by anyone knowing the naming convention.  Maintenance work is reduced, as future developers will not have to waste time figuring out what each field is.

prepared ByTsedale.B Con…  Whatever the naming convention, make sure it becomes a standard across the company and is followed by everyone in your department.The following paragraphs presents the conventions Interact uses and recommends for developing web applications. In no way are they imposed, or the only ones possible to use.

prepared ByTsedale.B General conventions The following conventions apply to all of the elements of a database:  All names used throughout the database should be lowercase only. This will eliminate errors related to case-sensitivity.  Separate name parts by underlines, never by spaces.This way, you improve the readability of each name (e.g. product_name instead of productname).You will not have to use parentheses or quotes to enclose names using spaces as well.The use of spaces in a database name is allowed only on some systems, while the underline is an alphanumeric character, allowed on any platform.  Do not use numbers in the names (e.g. product_attribute1).This is proof of poor design, indicating a badly divided table structure

prepared ByTsedale.B Con…  Do not use the dot (.) as a separator in names.This way you will avoid problems when trying to perform queries, as the dot is used to identify a field in a specific column.  Do not use any of the reserved words as names of database elements. For instance,using order as the name of a table that stores product orders from an online shop is bad practice,because order is also used in databse language to sort records (ascending or descending).

prepared ByTsedale.B Con…  When naming the elements, do not use long or awkward names. Keep them as simple as you can, while maintaining a clear meaning. It's also a good idea to use names which are close to the natural language.  Example,description_prd is certainly a better name for a column that stores product descriptions than dscr_pr or some generic name as field_1.

prepared ByTsedale.B Table Relationship  Relationships allow you to describe the connections between different database tables in powerful ways.  Once you’ve described the relationships between your tables, you can later leverage that information to perform powerful cross-table queries, known as joins.  A relationship is a logical connection between two tables.  Keys are fields that are part of a table relationship.There are two kinds of keys

prepared ByTsedale.B Primary key  A table can have only one primary key.  A primary key is used to identify each record that you store in the table.  It will not allow a duplication of the Primary Key thus make it unique.  Primary Key is the unique identification of one record.There is a uniquely identification number, such as  ID number  A serial number  A code that serves as a primary key

prepared ByTsedale.B Types of Database Relationships  One-to-one relationships: occur when each entry in the first table has one, and only one, counterpart in the second table.  One-to-many relationships: Is the most common type of database relationship. .  Many-to-many relationships: occur when each record in the first table corresponds to one or more records in the second table and each record in the second table corresponds to one or more records in the first table.

prepared ByTsedale.B Referential integrity  Referential integrity is a system of rules that Access uses to make sure that relationships between records in related tables are valid, and that you do not accidentally delete or change related data.You can set referential integrity when all the following conditions are true:  The matching field from the primary table is a primary key or has a unique index.  The related fields have the same data type.There are two exceptions.An AutoNumber field  can be related to a Number field that has a FieldSize property setting of Long Integer, and anAutoNumber field that has a FieldSize property setting of Replication ID can be related to a Number field that has a FieldSize property setting of Replication ID.  Both tables belong to the same Access database. If the tables are linked tables, they must be tables inAccess format, and you must open the database in which they are stored to set referential integrity. Referential integrity cannot be enforced for linked tables from databases in other formats.

prepared ByTsedale.B Ways to Add, Edit, and Delete records  There are several ways to update data in an Access database.You add a record to your database when you have a new item to track, such as a new contact to the Contacts table.When you add a new record,Access appends the record to the end of the table.  You also change fields to stay up-to-date, such as a new address or last name.To maintain data integrity, the fields in an Access database are set to accept a specific type of data, such as text or numbers.  If you don't enter the correct data type,Access displays an error message. Finally, you can delete a record when it is no longer relevant and to save space. You use a form to manually update data. Data entry forms can provide an easier, faster, and more accurate way to enter data. Forms can contain any number of controls such as lists, text boxes, and buttons. In turn, each of the controls on the form either reads data from or writes data to an underlying table field

prepared ByTsedale.B Understanding data entry symbols  The following table shows some of the record selector symbols you might see when updating data and what they mean

prepared ByTsedale.B Saving a database  Saving your work in Access is a little different from saving in most Office apps. Changes to data, the primary reason for saving your work in most apps, are automatically saved in In Access, instead of saving data changes, you save changes to the database design, or you save the whole database, data and all, with a new filename as a backup, or in a different format, such as an earlier Access file format, a database template, or a compiled database (a database where you can't change the design).You can also save individual database objects as new objects

prepared ByTsedale.B To be continue…….

Create database objects in web development and database administration .pptx

More Related Content

Similar to Create database objects in web development and database administration .pptx

Recently uploaded

Create database objects in web development and database administration .pptx