Lecture 1 2024-09-29 19_11_26.pdf

‫كلية الهندسة و تكنولوجيا المعلومات‬ ‫قسم هندسة امن السبراني‬ ‫‪Database systems‬‬ ‫‪Introduction to database systems‬‬ ‫م‪.‬م سالي صالح الدين‬ ‫‪Page 1 of 11‬‬ Database systems...

‫كلية الهندسة و تكنولوجيا المعلومات‬ ‫قسم هندسة امن السبراني‬ ‫‪Database systems‬‬ ‫‪Introduction to database systems‬‬ ‫م‪.‬م سالي صالح الدين‬ ‫‪Page 1 of 11‬‬ Database systems ‫ كلية الهندسة و تكنولوجيا المعلومات‬/ ‫جامعة الشعب‬ Ch 1 ‫قسم هندسة امن االسبراني‬ Introduction Database and database technology have major impact on the growing use of computer. It is fair to say that database play a critical role in almost all areas where computer is used, including business, electronic commerce, engineering, law, education, and library science. The word database is so commonly used that we must begin by defining what a database is. Database (DB): Is a collection of interrelated data stored together without harmful or unnecessary redundancy to serve multiple applications, the data stored so that they are independent of the applications which use it. Database Design and Data Redundancy Every organization needs to manage data about entities. An entity is a person, place, object, event or idea about which an organization stores data. Colleges maintain records regarding students, courses, faculty, equipment, and grades. Businesses need to keep track of customers, sales, employees, inventory and payments. A database management program is used to store, maintain and present data in an efficient and structured fashion. The elements of this structure are illustrated in Figure 1 and are described in the bulleted paragraphs be low. A field is an attribute of an entity. You may think of a field as a category of data. There are 8 fields in the Customers table in Figure 1, including First, Last and Birth Date. A record is a collection of related fields, each pertaining to the same entity. The bottom row in the Customers table is the collection of fields for customer Scott Owen. There are 17 records in the Customers table. A table is a collection of related records, each having the same field structure. As you see in Figure 1, a table is a two-dimensional structure where the columns represent fields and the rows represent records. Tables are used to store data. A database is a collection of related tables and other objects Page 2 of 11 Database systems ‫ كلية الهندسة و تكنولوجيا المعلومات‬/ ‫جامعة الشعب‬ Ch 1 ‫قسم هندسة امن االسبراني‬ The preceding discussion focused on a properly designed database, where each entity is placed in a separate table and where a common field is available to join related records between the tables. We now explore the perils of an improper database design. Take a few moments to inspect Figure 3. Notice that all of the fields now appear in a single table. Although this may seem simpler, it turns out to be a terrible idea. Look at the third and fourth rows in Figure 3. These two rows reflect the sales made to Kristin Reis. But look more closely and you’ll notice that Kristin’s information (First, Last, Street, City, State, Zip and Birth Date) is stored twice because she has been the customer in two different sales. If she had been the customer in 50 sales, this design would require that we store her name, address and birth date 50 different times! Storing the same field values more than once (unnecessarily) are referred to as data redundancy. Three problems are caused by data redundancy. The first is that storing values multiple times wastes space. Under a proper design (Figure 1), Kristin’s information is stored only once, in her record in the Customers table. Page 3 of 11 Database systems ‫ كلية الهندسة و تكنولوجيا المعلومات‬/ ‫جامعة الشعب‬ Ch 1 ‫قسم هندسة امن االسبراني‬ The second problem is that when a field value changes, multiple occurrences need to be updated. For example, if Kristin moves, we’ll need to change the values for her Street, City, State and Zip in multiple records. The third problem occurs if we forget to change the values in any of the records. The database would then have inconsistent data. Purpose of Database Systems: (Why DB?) The goal of a database system is to simplify and facilitate access to data from the centralized control there are some advantages such as: 1- Redundancy can be reduced: This data repetition may occur either if a field is repeated in two or more tables or if the field is repeated within the table. This can mean two different fields within a single database. Data redundancy is the repetition of data. 2- Inconsistency can be avoided: If we have redundant data in one table, at such time the DB is said to be Page 4 of 11 Database systems ‫ كلية الهندسة و تكنولوجيا المعلومات‬/ ‫جامعة الشعب‬ Ch 1 ‫قسم هندسة امن االسبراني‬ inconsistent. The problem occurs if we forget to change the values in any of the records. The database would then have inconsistent data. 3- The data can be shared: We mean that individual pieces of data in the DB may be shared among several different users and applications, in that each of those users may have access to the same piece of data. 4- Standards can be enforced: Standards are common practices that ensure the consistency and effectiveness of the database environment, such as database naming conventions. Procedures are defined, step-by-step instructions that direct the processes required for handling specific events, such as a disaster recovery plan. Failure to implement database standards and procedures. will result in a database environment that is confusing and difficult to manage. For example (communication, administration,)’ standards. 5- Security restriction can be applied: The DBA (Database Admin) can ensure that the only means of access to the DB is through the proper channels. The DBA can define security checks to be carried out whenever access is attempted to sensitive data. 6- Data Integrity The following categories of data integrity exist with each RDBMS − Entity Integrity − There are no duplicate rows in a table. Domain Integrity − Enforces valid entries for a given column by restricting the type, the format, or the range of values. Referential integrity − Rows cannot be deleted, which are used by other records. Page 5 of 11 Database systems ‫ كلية الهندسة و تكنولوجيا المعلومات‬/ ‫جامعة الشعب‬ Ch 1 ‫قسم هندسة امن االسبراني‬ 7- Data Independence: It refers to the immunity of user applications to changes made in the definition and organization of data. Physical data independence deals with hiding the details of the storage structure from user applications. Database Administrator DBA DBA is a person or persons responsible for the structure or schema of the DB also responsible for authorizing software access and hardware resource as needed. Another definition of DBA DBA: is a person responsible for the performance, integrity and security of a database. He will also be involved in the planning and developing of the DB, as well as troubleshooting any issues on behalf of the users. Database schema (Data Abstraction) Database Schema A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how the relations among them are associated. It formulates all the constraints that are to be applied on the data. A database schema defines its entities and the relationship among them. It contains a descriptive detail of the database, which can be depicted by means of schema diagrams. It’s the database designers who design the schema to help programmers understand the database and make it useful. Page 6 of 11 Database systems ‫ كلية الهندسة و تكنولوجيا المعلومات‬/ ‫جامعة الشعب‬ Ch 1 ‫قسم هندسة امن االسبراني‬ A database schema can be divided broadly into two categories − Physical Database Schema − This schema relates to the actual storage of data and its form of storage like files, indices, etc. It defines how the data will be stored in a secondary storage. Logical Database Schema − This schema defines all the logical constraints that need to be applied on the data stored. It defines tables, views, and integrity constraints. Database Instance It is important that we distinguish these two terms individually. Database schema is the skeleton of database. It is designed when the database doesn't exist at all. Once the database is operational, it is very difficult to make any changes to it. A database schema does not contain any data or information. Page 7 of 11 Database systems ‫ كلية الهندسة و تكنولوجيا المعلومات‬/ ‫جامعة الشعب‬ Ch 1 ‫قسم هندسة امن االسبراني‬ A database instance is a state of operational database with data at any given time. It contains a snapshot of the database. Database instances tend to change with time. A DBMS ensures that its every instance (state) is in a valid state, by diligently following all the validations, constraints, and conditions that the database designers have imposed. Instance: The data stored in database at a particular moment of time is called instance of database. Database schema defines the variable declarations in tables that belong to a particular database; the value of these variables at a moment of time is called the instance of that database. Database Management System (DBMS Overview) Database is a collection of related data and data is a collection of facts and figures that can be processed to produce information. Mostly data represents recordable facts. Data aids in producing information, which is based on facts. For example, if we have data about marks obtained by all students, we can then conclude about toppers and average marks. Database Management System (DBMS) is system software for creating and managing databases. The DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data. Characteristics Traditionally, data was organized in file formats. DBMS was a new concept then, and all the research was done to make it overcome the deficiencies in traditional style of data management. A modern DBMS has the following characteristics − Real-world entity − A modern DBMS is more realistic and uses real-world entities to design its architecture. It uses the behaviour and attributes too. For example, a school database may use students as an entity and their age as an attribute. Page 8 of 11 Database systems ‫ كلية الهندسة و تكنولوجيا المعلومات‬/ ‫جامعة الشعب‬ Ch 1 ‫قسم هندسة امن االسبراني‬ Relation-based tables − DBMS allows entities and relations among them to form tables. A user can understand the architecture of a database just by looking at the table names. Isolation of data and application − A database system is entirely different than its data. A database is an active entity, whereas data is said to be passive, on which the database works and organizes. DBMS also stores metadata, which is data about data, to ease its own process. Less redundancy − DBMS follows the rules of normalization, which splits a relation when any of its attributes is having redundancy in values. Normalization is a mathematically rich and scientific process that reduces data redundancy. Consistency − Consistency is a state where every relation in a database remains consistent. There exist methods and techniques, which can detect attempt of leaving database in inconsistent state. A DBMS can provide greater consistency as compared to earlier forms of data storing applications like file-processing systems. Query Language − DBMS is equipped with query language, which makes it more efficient to retrieve and manipulate data. A user can apply as many and as different filtering options as required to retrieve a set of data. Traditionally it was not possible where file-processing system was used. ACID Properties − DBMS follows the concepts of Atomicity, Consistency, Isolation, and Durability (normally shortened as ACID). These concepts are applied on transactions, which manipulate data in a database. ACID properties help the database stay healthy in multi-transactional environments and in case of failure. Page 9 of 11 Database systems ‫ كلية الهندسة و تكنولوجيا المعلومات‬/ ‫جامعة الشعب‬ Ch 1 ‫قسم هندسة امن االسبراني‬ Multiuser and Concurrent Access − DBMS supports multi-user environment and allows them to access and manipulate data in parallel. Though there are restrictions on transactions when users attempt to handle the same data item, but users are always unaware of them. Multiple views − DBMS offers multiple views for different users. A user who is in the Sales department will have a different view of database than a person working in the Production department. This feature enables the users to have a concentrate view of the database according to their requirements. Security − Features like multiple views offer security to some extent where users are unable to access data of other users and departments. DBMS offers methods to impose constraints while entering data into the database and retrieving the same at a later stage. DBMS offers many different levels of security features, which enables multiple users to have different views with different features. For example, a user in the Sales department cannot see the data that belongs to the Purchase department. Additionally, it can also be managed how much data of the Sales department should be displayed to the user. Since a DBMS is not saved on the disk as traditional file systems, it is very hard for miscreants to break the code. Users A typical DBMS has users with different rights and permissions who use it for different purposes. Some users retrieve data and some back it up. Page 10 of 11 Database systems ‫ كلية الهندسة و تكنولوجيا المعلومات‬/ ‫جامعة الشعب‬ Ch 1 ‫قسم هندسة امن االسبراني‬ The users of a DBMS can be broadly categorized as follows − Administrators − Administrators maintain the DBMS and are responsible for administrating the database. They are responsible to look after its usage and by whom it should be used. They create access profiles for users and apply limitations to maintain isolation and force security. Administrators also look after DBMS resources like system license, required tools, and other software and hardware related maintenance. Designers − Designers are the group of people who actually work on the designing part of the database. They keep a close watch on what data should be kept and in what format. They identify and design the whole set of entities, relations, constraints, and views. End Users − End users are those who actually reap the benefits of having a DBMS. End users can range from simple viewers who pay attention to the logs or market rates to sophisticated users such as business analysts. Page 11 of 11