In: Computers and Technology

Submitted By mariamSelim
Words 6699
Pages 27
The Relational Data Model and Relational Database Constraints
This chapter opens Part II of the book on relational databases. The relational model was first introduced by Ted Codd of IBM Research in 1970 in a classic research publication "System R4 Relational." (Codd 1970), and attracted immediate attention due to its simplicity, practicality, and mathematical foundation. The model uses the concept of a mathematical relation—which looks somewhat like a table of values—as its basic building block, and has its theoretical basis in set theory and first-order predicate logic. This is a classic example where sound theoretical principles are effectively applied for strong practical advantages. In this chapter we discuss the basic characteristics of the relational model and its constraints.
The first commercial implementations of the relational model became available in the early 1980s, such as the Oracle DBMS and the SQL/DS system on the MVS operating system by IBM. Since then, the model has been implemented in a large number of commercial systems. Current popular relational DBMSs (RDBMSs) include DB2 and Informix Dynamic Server (from IBM), Oracle and Rdb (from Oracle), and SQL Server and Access (from Microsoft). Some other commercial relational DBMSs used are Sybase (also known as Sybase SQL Server), Paradox, and Foxbase. Recently, some open source relational DBMSs, such as MySQL and PostGreSQL have also grown in popularity. At the time this chapter was written, the market share varied depending on platform; with DB2, Oracle, and Microsoft SQL Server dominating the market overall. [Note to editor: Would you like to include TM symbol in all trademarks? Also the small caps formatting, such as SQL and SQL can be made consistent. Personally, I prefer SQL over SQL but am…...

Similar Documents


...Client/Server is a system. It is not just hardware or software. It is not necessarily a program that comes in a box to be installed onto your computer’s hard drive. Client/Server is a conglomeration of computer equipment, infrastructure, and software programs working together to accomplish computing tasks which enable their users to be more efficient and productive. Client/Server applications can be distinguished by the nature of the service or type of solutions they provide. Client/Server Computing is new technology that yields solutions to many data management problems faced by modern organizations. Client/Server Computing: An Introduction, features objective evaluations and details of Client/Server development tools, used operating system, database management system and its mechanism in respect of Client/Server computing and network components used in order to build effective Client/Server applications. vi Preface Last but not the least, this work is primarily a joint work with a number of fellow teacher who have worked with us. My parents, wife Meera, and our children, Akanksha and Harsh. I am particularly grateful to Dr. A. P. Singh, Principal, Udai Pratap Inter College, Varanasi; Dr. D. S. Yadav, Sr. Lecturer, Department of Computer Science and Engineering, IET, Lucknow; Dr. A. K. Naiyak, Director IIBM, Patna, former President of IT and Computer Science Section of Indian Science Congress Association; Prof. A. K. Agrawal, Professor and Ex-Head of Department,......

Words: 79055 - Pages: 317


...the minimum, maximum, sum, and average salary for each job type. Resave lab_04_04.sql as lab_04_05.sql. Run the statement in lab_04_05.sql. SELECT job_id, ROUND(MAX(salary),0) "Maximum",                 ROUND(MIN(salary),0) "Minimum",                 ROUND(SUM(salary),0) "Sum",                 ROUND(AVG(salary),0) "Average"  FROM   employees         GROUP BY job_id;  6. Write a query to display the number of people with the same job. SELECT job_id, COUNT(*)  FROM   employees   GROUP BY job_id;  Generalize the query so that the user in the HR department is prompted for a job title. Save the script to a file named lab_04_06.sql.� SELECT job_id, COUNT(*)  FROM   employees  WHERE  job_id = '&job_title'  GROUP BY job_id;  Oracle Database 10g: SQL Fundamentals I A - 12 Practice 4: Solutions (continued) 7. Determine the number of managers without listing them. Label the column Number of  Managers. Hint: Use the MANAGER_ID column to determine the number of managers. SELECT COUNT(DISTINCT manager_id) "Number of Managers"  FROM   employees;   8. Find the difference between the highest and lowest salaries. Label the column DIFFERENCE.� SELECT   MAX(salary) - MIN(salary) DIFFERENCE  FROM     employees;  If you have time, complete the following exercises:� 9. Create a report to display the manager number and the salary of the lowest-paid employee for that manager. Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is $6,000 or......

Words: 569 - Pages: 3


...Introduction $  $  • Purpose of Database Systems • Data Definition Language • Data Manipulation Language • Transaction Management & ' & • Storage Management • Database Administrator • Database Users • Overall System Structure 1.1 Silberschatz, Korth and Sudarshan c 1997 Database Systems Concepts Database Management System (DBMS) • Collection of interrelated data • Set of programs to access the data • DBMS contains information about a particular enterprise • DBMS provides an environment that it both convenient and efficient to use Database Systems Concepts 1.2 Silberschatz, Korth and Sudarshan c 1997 ' & ' & Purpose of Database Systems $  $  Database management systems were developed to handle the following difficulties of typical file-processing systems supported by conventional operating systems. • Data redundancy and inconsistency • Difficulty in accessing data • Data isolation – multiple files and formats • Integrity problems • Atomicity of updates • Concurrent access by multiple users • Security problems Database Systems Concepts 1.3 Silberschatz, Korth and Sudarshan c 1997 View of Data An architecture for a database system view level view 1 view 2 … view n logical level physical level Database Systems Concepts 1.4 Silberschatz, Korth and Sudarshan c 1997 ' & ' & Levels of Abstraction $  $  • Physical level: describes how a record (e.g., customer) is stored. • Logical level: describes data stored in database, and the relationships among......

Words: 1023 - Pages: 5


...IT203 Database Development GRADED ASSIGNMENTS ------------------------------------------------- Graded Assignment Requirements This document includes all of the assignment requirements for the graded assignments in this course. Your instructor will provide the details about when each assignment is due. Unit 1 Assignment 1: Homework Learning Objectives and Outcomes Describe the role of software for computers. Identify the hardware associated with a computer. Describe how computers store data. Explain how programs work. Differentiate among machine language, assembly language, and high-level languages. Differentiate between compilers and interpreters. Identify the different types of software. Assignment Requirements Answer the following true-or-false questions. Required Resources * Textbook Submission Requirements Submit your answers on the answer sheet provided to your instructor at the beginning of Unit 2. Unit 1 True/False Questions Indicate whether the following statements are true or false on the student answer sheet. 1) At its simplest, a database can be seen as a collection of related data. 2) A delimited file is a file where the length of the file is limited. 3) A delimited file is one in which the data is separated by a character such as a comma. 4) All fixed length files are of the same length. 5) In relational databases, logical design is kept separate from physical design. 6) A......

Words: 7907 - Pages: 32


...needed to build a database for the scheduling process. * Describe the advantages and disadvantages of moving the schedule to a database. * Create a graphical representation of your proposed database environment. * Explain how referential integrity can prevent mistakes in a database. Do the advantages outweigh the difficulty of setting it up? * Impress your boss by creating a database in Microsoft Access that will match trainers with courses and meet the following requirements: * Two (2) tables, one (1) containing trainer information and the other containing course information. Create data for five (5) instructors and five (5) courses. The trainer data should include: employee ID number, last name, first name, department, and email address. The course data should include: course number, course name, trainer employee ID number, and training location. * A relationship should exist between the tables. * Use referential integrity to make sure that each course has a valid trainer assigned to it. Week 7 Assignment 4 * Discuss the requirements needed to build a database for the scheduling process. The first step in creating a database is creating a plan that serves both as a guide to be used when implementing the database and as a functional specification after it has been implemented. The complexity and detail of a design is dictated by the complexity and size of the database application as well as the user population. A database can be......

Words: 681 - Pages: 3


...CHAPTER 4 RELATIONAL DATABASES SUGGESTED ANSWERS TO DISCUSSION QUESTIONS 4.1 Contrast the logical and the physical view of data and discuss why separate views are necessary in database applications. Describe which perspective is most useful for each of the following employees: a programmer, a manager, and an internal auditor. How will understanding logical data structures assist you when designing and using database systems?</para></question><question id="ch04ques02" label="4.2"> Databases are possible because of their database management system (DBMS). As shown in Figure 4.2, the DBMS is a software program that sits between the actual data stored in the system and the application programs that use the data. As shown in Figure 4.4, this allows users to separate the way they view the data (called the logical view) from the way the data is actually stored (the physical view). The DBMS interprets the users' requests and retrieves, manipulates, or stores the data as needed. The two distinct views separate the applications from the physical information, providing increased flexibility in applications, improved data security, and ease of use. In a database system, the manager will rarely need to understand or be familiar with the physical view of the data. Nor, in most instances, will the internal auditor and the programmer as most everything they do involves the......

Words: 4830 - Pages: 20


...Information Systems 3 (database 3) Star Schema Draft 1 You must create a Star Schema University Attendance database for the following situation: The database must record attendance of students in classes, the time of the lectur e, the lecturer who taught the class, the room that was used and the subject for that lecture. For each subject you must record the total number of classes timetabled for the semester – this will be used to calculate Student attendance percentage and Lectu rer attendance percentage. An attendance fact must always contain a valid lecturer - id; we assume if one lecturer is absent then another will take his/her place. You may assume maximum room usage is 11 X 5 = 55 periods a week. You can work out the primary k ey of the fact table from the above specification. The following criteria must be met: 1) This is an individual assignment. 2) You must write SQL statements to e nter the data ; Try not to repeatedly enter the same thing for one class. 3) You will need reports to provide: a) % usage of rooms b) % attendance of students c) % attendance of lecturers d) At least 2 other reports of interest 4) You must enter at least 3 0 fact records (this is a factless fact table); in all 10 students, 5 different lecturers, 3 different rooms , 2 subjects and 10 dat e/time slots as your initial prototype system. 5) (Optional ). You must load your fact table from an operational......

Words: 304 - Pages: 2

Database a relation schema. For example, Student(StudId: integer, StudName: string, Major: string) • A relation schema specifies the name and the structure of the relation. • A collection of relation schemas is called a relational database schema. * 1. List the names of all students who have borrowed a book and who are CS majors. πStName(σSTUDENTS.StId=borrows.StId (σMajor=’CS’(STUDENTS)×borrows)) * 2. List the title of books written by the author ’Silberschatz’. πTitle(σAName=’Silberschatz’ (σhas-written.DocId=BOOKS.DocID(has-written×BOOKS))) or πTitle(σhas-written.DocId=BOOKS.DocID (σAName=’Silberschatz’(has-written)×BOOKS)) * 3. As 2., but not books that have the keyword ’database’. * . . . as for 2. . . . − πTitle(σdescribes.DocId=BOOKS.DocId (σKeyword=’database’(describes)×BOOKS)) * 4. Find the name of the youngest student. πStName(STUDENTS)− πS1.StName(σS1.Age>S2.Age(ρS1(STUDENTS)×ρS2(STUDENTS))) * 5. Find the title of the oldest book. πTitle(BOOKS)−πB1.Title(σB1.Year>B2.Year(ρB1(BOOKS)×ρB2(BOOKS))) * Relational algebra is a language that is being used to explain basic relational operations and principles. * It is procedural language which instructs the system to perform a specific sequence of tasks on the database. * Relational algebra is a procedural query language with five fundamental operations. These operations include project, select, Cartesian product, union and set difference. * Projection is a unary operation......

Words: 700 - Pages: 3


...transformation processes. In actual sense, relational database offers a solution to this menace. Data organization, keeping, storage, and retrieval among other essentials can easily be realized by adopting relational data management model. In this kind of model, tables are made use of in such a way that data items are formally described and organized according to their level of relations. The data item contained in a given table represents a relation. I am convinced beyond reasonable doubt that through the use of relational database model, an organization can be able to effectively manage their wide range of information encountered on a daily basis. Relational database model offers a more efficient means of managing data. Some of the huge benefits are discussed as follows. A relational database is a collection of relations or tables. The rows of a table in a relational database are known as tuples and each column of a table is called an attribute. By definition, a relation becomes a set of tuples having the same attributes. Operations, which can be performed on the relations are select, project and join. The join operation combines relations, the select queries are used for data retrieval and the project operation identifies attributes. The information oif the organization can be captured, manipulated, managed, and shared, and the value the database brings to the organization is immense. The advantages of a relational database are that they contribute to sound logical......

Words: 554 - Pages: 3


...Assignment: Databases Maurice Eugene Northam III DBM/380 January 12, 2015 Bill Enloe Databases Database Management Systems (DBMS) are mind boggling, mission-discriminating programming systems. Today's DBMS’s epitomize many years of scholastic of a, what's more, mechanical examination and exceptional corporate programming advancement. Database Management Systems were among the soonest broadly conveyed online server systems and, accordingly, have spearheaded configuration arrangements crossing not just information administration, additional system applications, working systems, and organized administrations. The early DBMS’s are among the most powerful programming systems in software engineering, and the thoughts and execution issues spearheaded for DBMS’s are broadly duplicated and reexamined. For various reasons, the least of database systems building design are not as extensively known as they ought to be. First and foremost, the connected database systems group is genuinely little. Since business compels just help a couple of contenders at the top of the line, just a modest bunch of fruitful DBMS’s usage exist. The group of individuals included in outlining and actualizing database systems is tight: numerous went to the same schools, took a shot at the same compelling exploration ventures, and worked together on the same business items. Second, scholastic treatment of database systems frequently disregards building issues. Course reading presentations of database......

Words: 955 - Pages: 4


...COURSE OUTLINE DATABASE SYSTEMS: DIS 308 INTRODUCTION Databases are part of our day to day lives. When we are registering as students, we are interacting with databases, when we go to the bank, pay our electricity bills or pay water bills our accounts are on a database, even when we are browsing the net; information is coming from several databases. Therefore, databases are sources of information kept on electronic storage media for easy retrieval by interested parties. OBJECTIVE The objective of this unit is to introduce students to the concepts, architecture and design methods of database systems. At the end of the course you will be expected to translate logical models into physical databases LECTURE ONE: Introduction to Databases * Database Terminologies * Tradition Files * Database Management Systems (DBMSs) * Characteristics of Database Approach * Database Users * Database Storage LECTURE TWO: Database Systems Concepts and Architecture * Data Models * Three Schema Architecture * Database Languages and Interfaces LECTURE THREE: Database System Environment * Types of Databases * Database System Utilities * Tools Environment and Communication Facilities * Centralized and Client Server architecture for DBMSs * Distributed Databases * Data Warehouse LECTURE FOUR: Database Design Process * Logical Database Design Process * Physical Database Design * Object-Oriented Database Design LECTURE FIVE: CAT LECTURE SIX: ......

Words: 252 - Pages: 2


...CHOICE. Choose the one alternative that best completes the statement or answers the question. 3) The UNION clause is used to: 3) B A) join two tables together to form one table. B) combine the output from multiple queries into a single result table. C) find all rows that do not match in two tables. D) none of the above. TRUE/FALSE. Write 'T' if the statement is true and 'F' if the statement is false. 4) SQL statements can be included in another language, such as C or Java. 4) T MULTIPLE CHOICE. Choose the one alternative that best completes the statement or answers the question. 5) A procedure is: 5) B A) given a unique name. B) called by name. C) stored within the database. D) all of the above. 6) The ________ DBA view shows information about all users of the database in Oracle. 6) B A) DBA_VIEWS B) DBA_USERS C) USERS D) DBA_INDEXES TRUE/FALSE. Write 'T' if the statement is true and 'F' if the statement is false. 7) User-defined functions can improve system performance because they will be processed as sets rather than individually, thus reducing system overhead. 7) T ESSAY. Write your answer in the space provided or on a separate sheet of paper. 8) What can be done with Persistent Stored Modules? PSM - Persistent Stored Module is a feature which provides users the ability to add their own extensions to the standard SQL. The Uses of PSM: • PSM becomes a valuable application development tool in case of applications......

Words: 696 - Pages: 3


...1.1 Define the following terms: data, database, DBMS, database system, database catalog, program-data independence, user view, DBA, end user, canned transaction, deductive database system, persistent object, meta-data, and transaction-processing application. • Data merupakan satu satuan informasi yang akan diolah dimana sebelum diolah dikumpulkan di dalam suatu file database. Pengumpulan data dilakukan secara sistematis menurut struktur file database. • Database merupakan kumpulan data yang terkait satu sama lain. Atau sekumpulan data yang terintegrasi yang diorganisasi untuk memenuhi kebutuhan pemakai untuk keperluan organisasi. • Dalam database pastinya mengenal istilah DBMS (Databse Management System) yang mana merupakan suatu koleksi dari program yang mana pengguna bisa mengcreate dan memaintain suatu database. Dalam DBMS terdiri atas proses-proses yang harus dilakuka nyaitu defining, constructing, manipulating dan sharing. Komponen-komponen DBMS yaitu terdiri atas hardware, software, data, prosedur, people. Fungsi dari defining yaitu DBMS harus dapat mengolah pendefinisian data. Fungsi dari manipulating DBMS harus dapat menangani permintaan-permintaan dari pemakai untuk mengakses data. Fungsi dari constructing yaitu DBMS harus mengontrol proses pengiriman data ke penyimpanan medium. Fungsi dari sharing yaitu database memperbolehkan banyak orang dan program untuk mengakses database. • Meta data: informasi terstruktur yang bertujuan untuk mendeskripsikan,......

Words: 1618 - Pages: 7


...may create and / or assume all necessary assumptions needed for the completion of this assignment. You are currently the Chief Information Officer (CIO) for an innovative Internet-based company with gross revenues of more than $35 million dollars per year. During an executive leadership meeting, you were told that your company will be merging with a multinational company of equal size and you will be responsible for developing a project plan to strategically integrate all systems, including databases and infrastructure. The Chief Executive Officer has given you sixty (60) days to deliver an information technology project plan in anticipation of the company merger. Your company will be expanding from one (1) floor to three (3) floors within six (6) months. Since this merger will be bringing two (2) fairly equal-sized companies together under one roof, the infrastructure must be redesigned to accommodate the increased needs. Your company currently uses operational systems and relational databases but desires to expand into data warehousing. All information technology (hardware and software) must be redesigned to meet organizational needs. The solution should be implemented in either a hosted solution, on-site solution, or a hybrid model. The CEO is expecting you to integrate different technologies from different solution providers and incorporate industry best practices in connection with the development of technological systems. In addition, the CEO is extremely interested in......

Words: 695 - Pages: 3


...Case Study: Database Development ALEXANDRA DESINORD PROFESSOR OSAMA MORAD CIS 515- STRAT PLAN FOR DBASE SYSTEMS Week 7 Assignment February 29, 2016 The more companies base their large elements of their business strategy around data, the need for IT team and executives with deep understanding of data and analytics is growing rapidly. Data changes all of the time. That is why it is essential that companies should try to stay up to par with the ever changing technology so that they can ensure that the system can withstand anything that comes about. Down below I will discuss briefly the various aspects of the SLDC and the tasks that can be completed to help solve certain common problems that exist from within. Customer data management is not high on many organizations' lists of priorities until it has to be. The term used in systems engineering, information systems and software engineering to describe a process for planning, creating, testing, and deploying an information system is called Systems development life cycle (SDLC). This term is also referred to as the application development life-cycle, which is a term used in systems engineering, information systems and software engineering to describe a process for planning, creating, testing, and deploying an information system. The task of optimizing the software development process has faced many obstacles. The main obstacle is a lack of accurate and complete data on the development process. The use of the......

Words: 2352 - Pages: 10