Database

1. Database CVSL For IB ITGS

2. Topics to be covered • “What is a database;” • Understand the difference between data and information; • What is the purpose of a database system; • Database definitions and fundamental building blocks; (c) Indrani@CVSL

3. What is a database A database is any organized collection of data.Some examples of databases you may encounter in your daily life are: • a telephone book • T.V. Guide • airline reservation system • motor vehicle registration records • Forms you are filling for Admission (c) Indrani@CVSL

4. Data vs. information:What is the difference? • What is data? • Data can be defined in many ways. Information science defines data as unprocessed information. • What is information? • Information is data that have been organized and communicated in a coherent and meaningful manner. • Data is converted into information, and information is converted into knowledge. • Knowledge; information evaluated and organized so that it can be used purposefully. (c) Indrani@CVSL

5. What is the ultimate purpose of a database management system? Is to transform Data Information Knowledge Action (c) Indrani@CVSL

6. Why do we need a database? • Keep records of • Students • Staff • Library • Stocks • To keep a record of activities and interventions; • Keep sales records; • Develop reports; • Perform search • Tracking a particular record (c) Indrani@CVSL

7. Example of Database Software • Ms Access • Oracle • SQL Server (c) Indrani@CVSL

8. Difference between Flat File and Database • In File System, files are used to store data while, collections of databases are utilized for the storage of data in DBMS. Although File System and DBMS are two ways of managing data, DBMS clearly has many advantages over File Systems. Typically when using a File System, most tasks such as storage, retrieval and search are done manually and it is quite tedious whereas a DBMS will provide automated methods to complete these tasks. Because of this reason, using a File System will lead to problems like data integrity, data inconsistency and data security, but these problems could be avoided by using a DBMS. Unlike File System, DBMS are efficient because reading line by line is not required and certain control mechanisms are in place. (c) Indrani@CVSL

9. DBMS vs. RDBMS • In 1970s, Edgar Frank Codd introduced the theory of relational database. Thirteen rules were defined by Codd for this relational theory or model. Relationships among different types of data Is the main requirement of the relational model. • RDMS can be termed as the next generation of database management system. DBMS is used as a base model in order to store data in a relational database system. However, complex business applications use RDBMS rather than DBMS. • Relationship among tables is maintained in a RDBMS whereas this not the case DBMS as it is used to manage the database. • • DBMS accepts the ‘flat file’ data that means there is no relation among different data whereas RDBMS does not accepts this type of design. • • DBMS is used for simpler business applications whereas RDBMS is used for more complex applications. • • Although the foreign key concept is supported by both DBMS and RDBMS but its only RDBMS that enforces the rules. • • RDBMS solution is required by large sets of data whereas small sets of data can be managed by DBMS. (c) Indrani@CVSL

10. What is the purpose of the database ? • A database management system (DBMS) provides you with the software tools you need to organize that data in a flexible manner. • It includes tools to add, modify or delete data from the database, ask questions (or queries) about the data stored in the database and produce reports summarizing selected contents. (c) Indrani@CVSL

11. Some Important Definitions • A File: A group or collection of similar records, like Student File, American History 1850-1866 file, Basic Food Group Nutrition File • A field: one category of information, i.e., Name, Address, Semester Grade, Academic topic • A record: one piece of data, i.e., one student's information, a recipe, a test question (c) Indrani@CVSL

12. What is Microsoft Access Used For? Microsoft Access is an information management tool that helps you store information for reference, reporting, and analysis.  Microsoft Access can help you overcome the limitations found when trying to manage large amounts of information in Microsoft Excel or other spreadsheet applications. (c) Indrani@CVSL

13. What is Microsoft Access made up of? • The Microsoft® Access Database is made up of 7 major components: • Tables; • Relationships; • Queries; • Forms; • Reports; • Macros; • Modules. (c) Indrani@CVSL

14. Tables • The tables are the backbone and the storage container of the data entered into the database. • The tables that contain data look a bit like a table in Microsoft® Word or a Microsoft® Excel Spreadsheet, when opened. They have columns and rows as does a table in Microsoft® Word and an Excel worksheet. Each of the columns will have a field name at the top and each of the rows will represent a record. • As an example: (c) Indrani@CVSL

15. Primary Key • The primary key of a relational table uniquely identifies each record in the table. • This can be a unique identification number such as a Medicare number, Employee ID number, Pension number, etc.: • A primary key cannot contain duplicate values, e.g. a person’s last name is not suitable as a primary key as there is often more than one person with the same last name; • A primary key cannot contain null values, therefore a field such as a phone number is not suitable, as you may not know the person’s phone number when you first enter them into the database; • Also, if the information contained in the Primary Key is likely to be altered, then it is best to avoid this field as well. • If a unique identifier cannot be identified (which is more than likely), you can add a field, which will automatically increase sequentially by one, thus providing the record with a unique identifier. (c) Indrani@CVSL

16. Foreign key • A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables • In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table. (c) Indrani@CVSL

17. Data type • The data type of a programming element refers to what kind of data it can hold and how that data is stored. Data types apply to all values that can be stored in computer memory or participate in the evaluation of an expression. • Example • Name – hold alphabets • Marks – number (c) Indrani@CVSL

18. MS ACCESS DATATYPES • AutoNumber    Numbers that are automatically generated for each record. • Currency    Monetary values. • Date/Time    Dates and times. • Hyperlink    Hyperlinks, such as e-mail addresses. • Memo    Long blocks of text and text that use text formatting. A typical use of a Memo field would be a detailed product description. • Number    Numeric values, such as distances. Note that there is a separate data type for currency. • OLE Object    OLE objects, such as Word documents. • Text    Short, alphanumeric values, such as a last name or a street address. • Yes/No    Boolean values. (c) Indrani@CVSL

19. You can view your Microsoft Access tables in two different views - Datasheet view and Design view. • Datasheet view : Access allow you to view the contents of a table. When opened, you will see something similar to a spreadsheet, with columns of data with the field names identifying each column, whilst each row is a single record within the table. This is called the datasheet view. • 'Design view' where you see the structure of the table rather than the contents. It shows the field names and their datatype. From design view you can add and delete fields within the table, although you have to be careful with deleting the primary key in the table as that would cause problem with data integrity. (c) Indrani@CVSL

20. Query • A query is a request for information from a database which matches a set of criteria. • You use queries all the time when you use a search engine such as Google. You type in the term that you want some information about and click 'search'. The search engine looks through its database and displays a list of results which match the term that you used. (c) Indrani@CVSL

21. Other objects of Ms Acess • Forms provide non-technical users the ability to add data to your database. • Reportreport is a document (paper or electronic) that presents data in an easy-to-read, professional format. Reports can be created within database applications such as Access. In this case the results of database queries are presented in an attractive format, with headings, fonts and colours designed to enhance understanding of the data. • A "macro" refers to a set of actions that can be run automatically, and on demand. You could make a macro run when a user presses a certain key, or you could make a macro run everytime your database is opened. • Module A module is a collection of user-defined functions, subroutines, and global variables written in VBA code. These objects can then be used/called from anywhere in your Access database. (c) Indrani@CVSL

22. Database integrity • Database integrity ensures that data entered into the database is accurate, valid, and consistent. Any applicable integrity constraints and data validation rules must be satisfied before permitting a change to the database. • Three basic types of database integrity constraints are: • Entity integrity, not allowing multiple rows to have the same identity within a table. • Domain integrity, restricting data to predefined data types, e.g.: dates. • Referential integrity, requiring the existence of a related row in another table, e.g. a customer for a given customer ID. (c) Indrani@CVSL

23. Data redundancy • Data redundancy occurs in database systems which have a field that is repeated in two or more tables. For instance, in case when customer data is duplicated and attached with each product bought then redundancy of data is a known source of inconsistency, since customer might appear with different values for given attribute. Data redundancy leads to data anomalies and corruption and generally should be avoided by design.Database normalization prevents redundancy and makes the best possible usage of storage. Proper use of foreign keys can minimize data redundancy and chance of destructive anomalies.[2] However, concerns of efficiency and convenience can sometimes result in redundant data design despite the risk of corrupting the data. (c) Indrani@CVSL

24. Data Reliability • In a database system, a method to ensure data integrity • is fundamental to providing database reliability and • security. In particular, as data is communicated or • distributed over networks, a method to validate information • as authentic is required. The value of a database is • dependent upon a user’s ability to trust the completeness • and soundness of the information contained in the data (c) Indrani@CVSL

25. Data matching • Data matching is a powerful administrative and law enforcement tool, allowing information from a variety of sources to be brought together, compiled and applied to a range of public policy purposes at vastly lower costs than manual methods, ensuring everyone pays their fair share of tax. (c) Indrani@CVSL

26. data mining • Generally, data mining (sometimes called data or knowledge discovery) is the process of analyzing data from different perspectives and summarizing it into useful information - information that can be used to increase revenue, cuts costs, or both. Data mining software is one of a number of analytical tools for analyzing data. It allows users to analyze data from many different dimensions or angles, categorize it, and summarize the relationships identified. Technically, data mining is the process of finding correlations or patterns among dozens of fields in large relational databases. (c) Indrani@CVSL

27. Extra reading • http://www.alrc.gov.au/publications/9.%20Overview%3A%20Impact%20of%20Developing%20Technology%20on%20Privacy/data-matching-and-data-mining (c) Indrani@CVSL

28. Data Security • Data security is the protection of the database from unauthorized users. Only the authorized persons are allowed to access the database. Most of the users are allowed to access a part of database i.e., the data that is related to them or related to theirdepartment. Mostly, the DBA or head of department can access all the data in the database. Some users may be permitted only to retrieve data, whereas others are allowed to retrieve as well as to update data. The database access is controlled by the DBA. He creates the-accounts of users and gives rights to access the database.  (c) Indrani@CVSL

29. Thank you (c) Indrani@CVSL