Database Concepts PDF

Database Concept Database Environment Imagine trying to operate An-Najah N. University without Zajel ! How can An-Najah process students, courses …. data? How can An-Najah stores it all, and then quickly retrieve just the facts that decision makers want to know, just when they want to know it?...

Database Concept Database Environment Imagine trying to operate An-Najah N. University without Zajel ! How can An-Najah process students, courses …. data? How can An-Najah stores it all, and then quickly retrieve just the facts that decision makers want to know, just when they want to know it? The answer is that they use Database Databases : Data : are raw facts Information : is the result of processing raw data to reveal its meaning Data needs to be managed Data Management is a discipline that focuses on the proper generation, storage, and retrieval of data. data management is a core activity for any business, government agency, service organization, or charity. Efficient data management typically requires the use of a computer database. A database is a shared, integrated computer structure that stores a collection of: 0 End-user data, that is, raw facts of interest to the end user. Metadata, or data about data, through which the end- user data are integrated and managed. A database management system (DBMS) is a collection of programs that manages the database structure and controls access to the data stored in the database. In a sense, a database resembles a very well- organized electronic filing cabinet in which powerful software, known as a database management system, helps manage the cabinet’s contents The DBMS serves as 1. the intermediary between the user and the database. The database structure itself is stored as a collection of files, and the only way to access the data in those files is through the DBMS. 2. the DBMS presents the end user (or application program) with a single, integrated view of the data in the database. 3. The DBMS receives all application requests and translates them into the complex operations required to fulfill those requests. 4. The DBMS hides much of the database’s internal complexity from the application programs and users. 5. The application program might be written by a programmer using a programming language such as Visual Basic.NET, Java, or C#, or it might be created through a DBMS utility program. a DBMS provides advantages such as: 1. Improved data sharing. 2. Improved data security. 3. Better data integration. 4. Minimized data inconsistency. Data inconsistency exists when different versions of the same data appear in different places. 5. Improved data access. The DBMS makes it possible to produce quick answers to ad hoc queries. From a database perspective, a query is a specific request issued to the DBMS for data manipulation—for example, to read or update the data. Simply put, a query is a question, and an ad hoc query is a spur-of-the-moment question. The DBMS sends back an answer (called the query result set) to the application. 6. Improved decision making. Better-managed data and improved data access make it possible to generate better-quality information, on which better decisions are based. The quality of the information generated depends on the quality of the underlying data. Data quality is a comprehensive approach to promoting the accuracy, validity, and timeliness of the data. While the DBMS does not guarantee data quality, it provides a framework to facilitate data quality initiatives. 7. Increased end-user productivity. The availability of data, combined with the tools that transform data into usable information, empowers end users to make quick, informed decisions that can make the difference between success and failure in the global economy. 1. Lengthy development times. 2. Difficulty of getting quick answers. 3. Lack of security and limited data sharing. 4. Extensive programming. Making changes to an existing file structure can be difficult in a file system environment. For example, changing just one field in the original CUSTOMER file would require a program that: 1. Reads a record from the original file. 2. Transforms the original data to conform to the new structure’s storage requirements. 3. Writes the transformed data into the new file structure. 4. Repeats steps 2 to 4 for each record in the original file. A file system exhibits structural dependence, which means that access to a file is dependent on its structure. For example, adding a customer date-of-birth field to the CUSTOMER file shown in Figure 1.3 would require the four steps described in the previous section. Given this change, none of the previous programs will work with the new CUSTOMER file structure. Therefore, all of the file system programs must be modified to conform to the new file structure. In short, because the file system application programs are affected by change in the file structure, they exhibit structural dependence. Conversely, structural independence exists when it is possible to make changes in the file structure without affecting the application program’s ability to access the data. Even changes in the characteristics of data, such as changing a field from integer to decimal, require changes in all the programs that access the file. Because all data access programs are subject to change when any of the file’s data storage characteristics change (that is, changing the data type), the file system is said to exhibit data dependence. Conversely, data independence exists when it is possible to make changes in the data storage characteristics without affecting the application program’s ability to access the data. The practical significance of data dependence is the difference between the logical data format (how the human being views the data) and the physical data format (how the computer must work with the data). Any program that accesses a file system’s file must tell the computer not only what to do but also how to do it.  Data redundancy exists when the same data are stored unnecessarily at different places. Uncontrolled data redundancy sets the stage for: Poor data security. Data inconsistency. ( lack Data Integrity )  Continue. Data anomalies. A data anomaly develops when not all of the required changes in the redundant data are made successfully. The data anomalies defined as follows: - Update anomalies. -Insertion anomalies. -Deletion anomalies. Data modeling, as introduced later in this text, is the most common method of documenting database designs. Using a standardized data-modeling technique ensures that the data model fulfills its role in facilitating communication among the designer, user, and developer. The data model also provides an invaluable resource when maintaining or modifying a database as business requirements change Use MySQL Example Data modeling, the first step in designing a database, refers to the process of creating a specific data model for a determined problem domain. (A problem domain is a clearly defined area within the real-world environment, with well-defined scope and boundaries, that is to be systematically addressed.) A data model is a relatively simple representation, usually graphical, of more complex real-world data structures. In general terms, a model is an abstraction of a more complex real-world object or event. A model’s main function is to help you understand the complexities of the real-world environment. Within the database environment, a data model represents data structures and their characteristics, relations, constraints, transformations, and other constructs with the purpose of supporting a specific problem domain. Data modeling is an iterative, progressive process. You start with a simple understanding of the problem domain, and as your understanding of the problem domain increases, so does the level of detail of the data model. Done properly , the final data model is in effect a “blueprint” containing all the instructions to build a database that will meet all end-user requirements. This blueprint is narrative and graphical in nature, meaning that it contains both text descriptions in plain, unambiguous language and clear, useful diagrams depicting the main data elements. The basic building blocks of all data models are 1. entities, 2. attributes, 3. relationships, 4. and constraints. An entity is anything (a person , a place, a thing, or an event) about which data are to be collected and stored. An entity represents a particular type of object in the real world. entities are “distinguishable”— that is, each entity occurrence is unique and distinct. For example, a CUSTOMER entity would have many distinguishable customer occurrences, such as John Smith, Pedro Dinamita, Tom Strickland, etc. Entities may be physical objects, such as customers or products, but entities may also be abstractions, such as flight routes or musical concerts.  Cont. An attribute is a characteristic of an entity. For example, a CUSTOMER entity would be described by attributes such as customer last name, customer first name, customer phone, customer address, and customer credit limit. Attributes are the equivalent of fields in file systems. A relationship describes an association among entities. For example, a relationship exists between customers and agents that can be described as follows: an agent can serve many customers, and each customer may be served by one agent. GIVE ME MORE EXAMPLES Examples  Cont. Data models use three types of relationships: 1. one-to-many (1:M or1..*) 2. many-to-many (M:N or *..* , M:M ) 3. and one-to-one. (1:1or1..1) Cont. Cont. How do you properly identify entities, attributes, relationships, and constraints? The first step is to clearly identify the business rules for the problem domain you are modeling. A business rule is a brief, precise, and unambiguous description of a policy, procedure, or principle within a specific organization. In a sense, business rules are misnamed: they apply to any organization, large or small—a business, a government unit, a religious group, or a research laboratory—that stores and uses data to generate information. Examples of business rules are as follows: A customer may generate many invoices. An invoice is generated by only one customer. A training session cannot be scheduled for fewer than 10 employees or for more than 30 employees. Note that those business rules establish entities, relationships, and constraints. For example, the first two business rules establish two entities (CUSTOMER and INVOICE) and a 1:M relationship between those two entities. The third business rule establishes a constraint (no fewer than 10 people and no more than 30 people), two entities (EMPLOYEE and TRAINING), and a relationship between EMPLOYEE and TRAINING. Business rules set the stage for the proper identification of entities, attributes, relationships, and constraints. In the real world, names are used to identify objects. If the business environment wants to keep track of the objects, there will be specific business rules for them. As a general rule, a noun in a business rule will translate into an entity in the model, and a verb (active or passive) associating nouns will translate into a relationship among the entities. For example, the business rule “a customer may generate many invoices” contains two nouns (customer and invoices) and a verb (generate) that associates the nouns. From this business rule, you could deduce that: 1. Customer and invoice are objects of interest for the environment and should be represented by their respective entities. 2. There is a “generate” relationship between customer and invoice. Business Rules examples every Order must be associated with a valid Product. This prevents invalid Orders being entered into the Database. These Rules can be understood by both the Users and the Database designer. When you extend a Database Schema, it is always valuable to keep these Rules up-to-date. A. SCOPE OF THE DATABASE SCHEMA: The Scope is buying products from Amazon.com and Starbucks. B. THE THINGS OF INTEREST include -: Addresses. Customers. Deliveries. Orders. Payments. Products. Suppliers. C. These THINGS are Related as follows -: A Customer can have zero,one or many ORDERS. A CUSTOMER_ADDRESS can be associated with only one ADDRESS. A CUSTOMER_ADDRESS can be associated with only one CUSTOMER. A DELIVERY is associated with just one ORDER. An ORDER must be associated with one and only one CUSTOMER. In different applications in the real world, it is possble that an ORDER can be associated with more than one CUSTOMER, but in our example, it's only one. An ORDER can be associated with one or many PRODUCTS. A PAYMENT must be associated with one and only one ORDER. D. Other Characteristics of these THINGS include -: A BOOK can have one and only one ISBN.  Command line tool that process user’s SQL statements Requires Oracle account DDL Data Definition SQL DML Data Manipulation DCL Data Control 1 Structured Query Language (SQL)  The standard query language for creating and manipulating and controlling data in relational databases MS Access, Oracle, Sybase, Informix, etc. specified by a command-line tool or is embedded into a general purpose programming language, C, Pascal, Java, Perl...  Data Definition Language (DDL) Used to create (define) data structures such as tables, indexes, clusters  Data Manipulation Language (DML) is used to store, retrieve and update data from tables  Data Control Language used to control the access to the database objects created using DDL and DML 2 3 4 5 6 Same as your pegasus password Password: MBAgrad2004 7 Changing SQL*Plus password passw command Getting help Help command 8 Using Oracle Developer Interface 9 10 11 12 13 14 15 16 17 18 19 Summary 20 Relational Model ER-Diagram DreamHome Database Relational Model ER-Diagram Part 2 the entity relationship(ER) model, or ERM, has become a widely accepted standard for data modeling. ER models are normally represented in anentity relationship diagram(ERD), which uses graphical representations to model database components. Tiny College Example 1 2 3 4 5 6 7 8 9 10 11 12 If title varchar2(15) you cant decrease it for example: 13 An attempt to decrease retail from (7,2) to (5,1) 14 15 16 17 18 19 20 1 2 3 4 5 6 7 1 2 We can add all constraints in the Create command From the beginning We will see How Later 3 If we have a row in customer table for a customer with customer# 1020 if we insert (add) a new customer data with customer# 1020 using insert command an Error occurs ( Duplicate values) see example bellow 4 But, If we want to insert a new customer data with customer# 1022 5 6 7 8 9 10 11 12 13 14 CHAPTER 5 DATA MANIPULATION AND TRANSACTION CONTROL 1 2 Accmanager(amid, amfirst, amlast, amedate, amsal, amcomm,region 3 4 5 6 7 8 9 10 11 12 13 14 15 CHAPTER 11 Group Functions 1 2 3 SUM, AVG, COUNT, MAX, MIN For row by row condition For group of rows condition 4  ‫قاعدة بيانات موردي القطع‬ ‫الموردون‬ ‫القطع‬ ‫القطع التي وردت‬ ‫‪5‬‬ 6  Display the total profit of the supplied part for part number 100 Display the total sales from supplier number 4. 7  Display supplier name and total sales of supplier number 4. Display the total sales of part Bolt. Display the average profit of parts by supplier number 4 Display the average profit of parts by supplier number 4 with 2 decimal places 8 7600 / 4 = 1900 ( Null values are not counted in avg function 7600 / 5 = 1520 ( Null values become zero) 9  Get the total number of suppliers. Get the total number of suppliers currently supplying parts. Get the total number of part number 200 was supplied. How many times part number 200 was supplied? Get the total quantity part number 200 was supplied. What is the total quantity of part number 200 was supplied? 10 Get the highest (max) profit that was achieved from all suppled parts and the supplier number and part number for that highest profit. Get part number and its name for the highest profit that was achieved from all suppled parts. Get part number, name, color, weight for the lowest profit that was achieved of all suppled parts 11 Get part number, name, city for the lowest quantity that was ordered for of all suppled parts from Paris Get part number, name, city for the highest quantity that was ordered for of all suppled parts from Paris Get the total quantities that was ordered for all suppled parts from London 12 For all parts get part number and its total quantities that was supplied The result of this command is Error in Oracle But in Online SQL editor gives the following wrong result. This total for all parts supplied not for part number 100 This total for all parts supplied not for parts from London 13 To write the correct SQL command that answer this requirement For all parts get part number and its total quantities that was supplied We have to use Group By Clause Select xxx from yyy Group by xxx For all parts get part city and its total quantities that was supplied 14  For all supplied parts get supplier names and their total profit achieved by them For all parts suppled get part names and their average cost and price. 15 For all parts suppled get part number and the total profit of that part which is greater than or equal to 1500. For all parts suppled get part name and the total quantities of that part which is greater than or equal to 900. 16 For all London’s parts suppled get part name and the total quantities of that part which is greater than or equal to 200. 17 18 1) List all supplier numbers and the part numbers supplied. 2) Get supplier numbers for suppliers who supply part number 200. 3) Get supplier names and status for all suppliers who have a status between 15 and 25 inclusive. 4) Get supplier numbers and status for suppliers in Paris. 5) Get part numbers for all parts supplied. (No Duplication). 6) For all parts, get the part number and the weight of the part in grams ( part weights in the part table are given in pounds ). 7) Get supplier numbers for suppliers in Paris with status greater than 20. 8) Get supplier numbers and status for suppliers in Paris, in descending order of status. 9) Get all combinations of supplier and part information such that the supplier and part in question are located in the same city (co-located). 10) Get all combinations of supplier and part information such that the supplier and part in question are located in the same city, but omitting suppliers with status 20. 11) Get all pairs of supplier numbers such that the two suppliers concerned are co-located. 12) Get all part numbers and the supplier names for parts supplied by a supplier in London. 13) Get the total number of suppliers. 14) Get the total number of suppliers currently supplying parts. 15) Get the number of shipments ( ‫ )شحنات نقلت‬for part number 200. 16) Get the total quantity of part number 200 supplied. 17) For each part supplied, get the part number and the total quantity for that part. 19 1) select sno, pno from SP; 2) select distinct sno from SP where pno= 200; 3) select sno, status from S where status between 15 and 25; 4) Select sno, status from s where upper(city) = ‘PARIS’ 5) Select distinct pno from sp; 6) Select pno, weight, Weight * 453.592 from p; 7) Select sno , status where status > 20 and city = ‘Paris’ 8) Select sno , status where status > 20 and city = ‘Paris’ order by status desc; 9) Select S.* , P.* from S join P using(city); 10) Select S.* , P.* from S join P using(city); and status 20; 11) Select a.* , b.* from S a join s b using(city); 12) Select SP.pno , S.sname from SP join S using(sno) and S.city =‘London’; 13) Select count(*) from S ; 14) Select count( distinct sno ) from SP; 15) Select count(pno) from SP where pno = 200; 16) Select sum(qty) from sp where pno = 200; 17) Select pno, sum(qty) from sp Group by pno; 20 For each part supplied get part number, total number of orders , total quantity, total profit, max profit, min profit, average profit. 21 For each part supplied with total number of orders greater than 2 get part number, total number of orders , total quantity, total profit, max profit, min profit, average profit. For each part supplied with total number of orders greater than the total number of orders of part number 400 get part name, the total number of orders , total quantity, total profit, max profit, min profit, average profit. 22 CHAPTER 8 RESTRICTING ROWS AND SORTING DATA 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 CHAPTER 9 JOINING DATA FROM MULTIPLE TABLES 1 2 See Next Slide 3 4  select customers.customer_id, first_name, order_id, item, orders.customer_id from customers, orders; select customers.customer_id, first_name, order_id, item, orders.customer_id from customers cross join orders; 5 6 Use Where Cond. The common column must be be equals 7 This is the correct one 8  We can add more contions Use the above database schema to write the select statement that display customer names and the book titles they ordered. Select c.FirstName, c.LastName, b.Title From Customers c, Orders o, OrderItems ot, Books b Where c.customer# = o.customer# And o.order# = ot.order# And ot.ISBN = b.ISBN; 9 10 Promotion Table 11  Write the select statement that display book titles and its gift if the retail price of the book in the corresponding retail price range. Traditional Non-equality Joins Select b.Title, p.Gift From books b, promotion p Where b.retail between p.Minretail and p.Maxretail; 12 13 14 15 16 Join Complete Example 17 18 19 You can do it in another way 20 21 CHAPTER 10 Selected Single-Row Functions 1 2 Data when stored in database tables “JOHN” is not equal “John” and not equal also to “john” ‘A’ is < ‘a’ To guarantee the match when you search for ‘John” and you don’t know how it is stored we use Select * from Customers Where upper(firstname) = ‘JOHN’; 3 4 5 6 In Oracle : the standard function is inst(st1,st2) but we can use also instr( st1 , st2, pos, c) st1: The original string which you want to search in St2 : The string or char which you want to search for pos: start read from pos c: the occurrence -> the first the second or the third occurrence Select customer_name , instr(customer_name, ‘,’) from customers 7 For all items that have comma in their names 8 9 10 11 12 13 14 15 16 17 18 19 20 NVL :Convert Null value to a value NVL( a , 9 ) give 9 if null 21 22 23