...1. List the number and name of all sales reps. PROJECT Rep OVER (RepNum, LastName, FirstName) GIVING Answer 2. List all information from the Part table for part FD21. SELECT Part WHERE PartNum = 'FD21' GIVING Answer 3. List the order number, order date, customer number, and customer name for each order. JOIN Orders, Customer WHERE Orders.CustomerNum=Customer.CustomerNum GIVING Temp PROJECT Temp OVER (OrderNum, OrderDate, CustomerNum, CustomerName ) GIVING Answer 4. List the order number, order date, customer number, and customer name for each order placed by any customer represented by the sales rep whose last name is Kaiser. JOIN Orders, Customer WHERE Orders.CustomerNum=Customer.CustomerNum GIVING Temp1 JOIN Temp1, Rep WHERE Temp1.RepNum=Rep.RepNum GIVING Temp2 SELECT Temp2 WHERE Rep.LastName= ‘Kaiser’ GIVING Temp3 PROJECT Temp3 OVER (OrderNum, OrderDate, CustomerNum, CustomerName) GIVING Answer 5. List the number and date of all orders that were placed on 10/20/2013 or that were placed by a customer whose rep number is 20. SELECT Orders WHERE OrderDate= ‘10/20/2013’ GIVING Temp1 PROJECT Temp1 OVER (OrderNum, OrderDate) GIVING Temp2 JOIN Orders, Customer WHERE Orders.CustomerNum=Customer.CustomerNum GIVING Temp3 SELECT Temp3 WHERE RepNum= ‘20’ GIVING Temp4 PROJECT Temp4 OVER (OrderNum, OrderDate) GIVING Temp5 UNION Temp2 WITH Temp5 GIVING Answer 6. List the number and date of all orders that were placed on 10/20/2013 by a customer...
Words: 352 - Pages: 2
...1 INTRODUCTION SQL is divided into the following Data Definition Language (DDL) Data Manipulation Language (DML) Data Retrieval Language (DRL) Transaction Control Language (TCL) Data Control Language (DCL) DDL -- create, alter, drop, truncate, rename DML -- insert, update, delete DRL -- select TCL -- commit, rollback, savepoint DCL -- grant, revoke CREATE TABLE SYNTAX Create table (col1 datatype1, col2 datatype2 …coln datatypen); Ex: SQL> create table student (no number (2), name varchar (10), marks number (3)); INSERT This will be used to insert the records into table. We have two methods to insert. a) By value method By address method USING VALUE METHOD Syntax: insert into (table_name) values (value1, value2, value3 …. Valuen); © Copy rights are reserved. 2 Ex: SQL> insert into student values (1, ’sudha’, 100); SQL> insert into student values (2, ’saketh’, 200); To insert a new record again you have to type entire insert command, if there are lot of records this will be difficult. This will be avoided by using address method. b) USING ADDRESS METHOD Syntax: insert into (table_name) values (&col1, &col2, &col3 …. &coln); This will prompt you for the values but for every insert you have to use forward slash. Ex: SQL> insert into student values (&no, '&name', &marks); Enter value for no: 1 Enter value for name: Jagan Enter value for marks: 300 old new ...
Words: 42387 - Pages: 170
...released its own system called Essbase using the OLAP theoretical background and functionality. In 1998, Microsoft stepped in for upgrading and advancement in the OLAP technology. Microsoft worked on the mainstream idea and developed highly advanced online analytical system that is deployed in many large organizations today. Types of OLAP There are 3 types of the on-line analytical systems each with different properties according to the level of use. Multi-dimensional OLAP Multi-dimensional OLAP is also known as MOLAP in short. It is a classic type of leading on-line analytical systems. Instead of storing the data in relational form, it has the ability to store data in the multi-dimensional array that is highly optimized. This makes the data storage more efficient and quick. . Relational OLAP Relational OLAP (ROLAP) works similar to the relational...
Words: 824 - Pages: 4
...Folly words widow one downs few age every seven. If miss part by fact he park just shew. Discovered had get considered projection who favourable. Necessary up knowledge it tolerably. Unwilling departure education is be dashwoods or an. Use off agreeable law unwilling sir deficient curiosity instantly. Easy mind life fact with see has bore ten. Parish any chatty can elinor direct for former. Up as meant widow equal an share least. Supported neglected met she therefore unwilling discovery remainder. Way sentiments two indulgence uncommonly own. Diminution to frequently sentiments he connection continuing indulgence. An my exquisite conveying up defective. Shameless see the tolerably how continued. She enable men twenty elinor points appear. Whose merry ten yet was men seven ought balls. As it so contrasted oh estimating instrument. Size like body some one had. Are conduct viewing boy minutes warrant expense. Tolerably behaviour may admitting daughters offending her ask own. Praise effect wishes change way and any wanted. Lively use looked latter regard had. Do he it part more last in. Merits ye if mr narrow points. Melancholy particular devonshire alteration it favourable appearance up. May musical arrival beloved luckily adapted him. Shyness mention married son she his started now. Rose if as past near were. To graceful he elegance oh moderate attended entrance pleasure. Vulgar saw fat sudden edward way played either. Thoughts smallest at or peculiar relation breeding...
Words: 265 - Pages: 2
...RELATIONAL ALGEBRA Query Language It is a Language in which a user request information from the database. These languages are typically of a level higher than that of the standard programming language. It is divided into either procedural or non-procedural language. In the procedural Language, the user instructs the system to perform the sequence of operation on the database to compute a desired result. In a non-procedural Language, the user describes the information desired without giving a specific procedural for obtaining the information. Relational Algebra Definition It is a procedure language. It consists of a set of operations that take one or more relations as input and produce a new relation as a result. The fundamental operations of relational algebra are select, project, union, set difference, Cartesian product and rename. Other operations are: Set intersection, natural join, division and assignment. • The basic set of operations for the relational model is known as the relational algebra. These operations enable a user to specify basic retrieval requests. • The result of retrieval is a new relation, which may have been formed from one or more relations. The algebra operations thus produce new relations, which can be further manipulated using operations of the same algebra. • A sequence of relational algebra operations forms a relational algebra expression, whose result will also be a relation that represents the result of a database...
Words: 305 - Pages: 2
...Chapter 3 The Relational Model Review Questions 3.1 Discuss each of the following concepts in the context of the relational data model: (a) Relation (b) Attribute (c) Domain (d) Tuple (e) Intension and Extension (f) Degree and Cardinality. Each term defined in Section 3.2.1. 3.2 Describe the relationship between mathematical relations and relations in the relational data model? Let D1, D2, . . . , Dn be n sets. Their Cartesian product is defined as: D1 D2 . . . Dn {(d1, d2, . . . , dn) | d1 D1, d2 D2, . . . , dn Dn} Any set of n-tuples from this Cartesian product is a relation on the n sets. Now let A1, A2, . . ., An be attributes with domains D1, D2, . . . , Dn. Then the set {A1:D1, A2:D2, . . . , An:Dn} is a relation schema. A relation R defined by a relation schema S is a set of mappings from the attribute names to their corresponding domains. Thus, relation R is a set of n-tuples: (A1:d1, A2:d2, . . . , An:dn) such that d1 D1, d2 D2, . . . , dn Dn Each element in the n-tuple consists of an attribute and a value for that attribute. Discussed fully in Sections 3.2.2 and 3.2.3. 3.3 Describe the differences between a relation and a relation schema. What is a relational database schema? A relation schema is a named relation defined by a set of attribute and domain name pairs. A relational database schema is a set of relation schemas, each with a distinct name. Discussed in Section 3.2.3. 3.4 Discuss the properties...
Words: 3750 - Pages: 15
...Data Base Management Systems (DBMS) Study Material (Objective Type questions with Answers) Shared by Akhil Arora Powered by www.Gr8AmbitionZ.com your A to Z competitive exam guide DMBS Ojbective Type Questions - Akhil Arora Q.1 In the relational modes, cardinality is termed as: (A) Number of tuples. (C) Number of tables. Ans: A Q.2 Relational calculus is a (A) Procedural language. (C) Data definition language. Ans: B Q.3 The view of total database content is (A) Conceptual view. (C) External view. Ans: A (B) Number of attributes. (D) Number of constraints. (B) Non- Procedural language. (D) High level language. (B) Internal view. Q.4 Cartesian product in relational algebra is (C) a Ternary operator. 8A m (A) a Unary operator. Ans: B Cartesian product in relational algebra is a binary operator. (It requires two operands. e.g., P X Q) Q.5 DML is provided for (A) Description of logical structure of database. (B) Addition of new structures in the database system. Gr (C) Manipulation & processing of database. (D) Definition of physical structure of database system. for more materials visit us at www.Gr8AmbitionZ.com bit (D) not defined. (D) Physical View. (B) a Binary operator. ion Z. co m Database Objective type questions DMBS Ojbective Type Questions - Akhil Arora Ans: C DML is provided for manipulation & processing of database. language commands as its name) Q.6 ‘AS’ clause is used in SQL for (A)...
Words: 3574 - Pages: 15
...Statistical Databases Jaideep Srivastava and Hung Q. Ngo, Department of Computer Science, University of Minnesota, 200 Union street, EE/CS Building, room 4-192, Minneapolis, MN 55455 e-mail: srivasta, hngo @cs.umn.edu, ¡ 1 Introduction A statistical database management system (SDBMS) is a database management system that can model, store and manipulate data in a manner well suited to the needs of users who want to perform statistical analyses on the data. Statistical databases have some special characteristics and requirements that are not supported by existing commercial database management systems. For example, while basic aggregation operations like SUM and AVG are part of SQL, there is no support for other commonly used operations like variance and co-variance. Such computations, as well as more advanced ones like regression and principal component analysis, are usually performed using statistical packages and libraries, such as SAS [1] and SPSS [2]. From the end user’s perspective, whether the statistical calculations are being performed in the database or in a statistical package can be quite transparent, especially from a functionality viewpoint. However, once the datasets to be analyzed grow beyond a certain size, the statistical package approach becomes infeasible, either due to its inability to handle large volumes of data, or the unacceptable computation times which make interactive analysis impossible. With the increasing sophistication of data collection instrumentation...
Words: 11702 - Pages: 47
...Contents Preface 1 Chapter 1 Introduction Exercises 4 Chapter 2 Entity Relationship Model Exercises 9 Chapter 3 Relational Model Exercises 30 Chapter 4 SQL Exercises 42 Chapter 5 Other Relational Languages Exercises 58 Chapter 6 Integrity and Security Exercises 74 iii iv Contents Chapter 7 Relational-Database Design Exercises 84 Chapter 8 Object-Oriented Databases Exercises 98 Chapter 9 Object-Relational Databases Exercises 109 Chapter 10 XML Exercises 119 Chapter 11 Storage and File Structure Exercises 129 Chapter 12 Indexing and Hashing Exercises 141 Chapter 13 Query Processing Exercises 155 Chapter 14 Query Optimization Exercises 166 Chapter 15 Transactions Exercises 175 Chapter 16 Concurrency Control Exercises 182 Chapter 17 Recovery System Exercises 194 Contents v Chapter 18 Database System Architectures Exercises 201 Chapter 19 Distributed Databases Exercises 208 Chapter 20 Parallel Databases Exercises 217 Chapter 21 Application Development and Administration Exercises 225 Chapter 22 Advanced Querying and Information Retrieval Exercises 232 Chapter 23 Advanced Data Types and New Applications Exercises 241 Chapter 24 Advanced Transaction Processing Exercises 249 C H A P T E R 1 Introduction Chapter 1 provides a general overview of the nature and purpose of database systems. The most important concept in...
Words: 41091 - Pages: 165
...textbook: Laboratory Manual Chapter Elmasri/Navathe 6th Edition Chapter(s) Chapter 1 Chapter 2 Chapter 3 Chapter 4 Chapter 5 Chapter 6 Chapter 7 Chapter 8 Chapters 7, 8, and 9 Chapters 3, 6, and 26 Chapters 4, 5, and 13 Chapters 4, 5, and 14 Chapters 15 and 16 Chapter 11 Chapter 12 Chapters 13 and 14 Chapter 1 presents ERWin, a popular data modeling software that allows database designers to represent Entity-Relationship diagrams and automatically generate relational SQL code to create the database in one of several commercial relational database management systems such as Oracle or Microsoft SQLServer. The material presented in this chapter is tutorial in nature and covers the COMPANY database design of the Elmasri/Navathe text in detail. Chapter 2 presents three interpreters that can be used to execute queries in Relational Algebra, Domain Relational Calculus, and Datalog. These interpreters are part of a Java package that includes a rudimentary database engine capable of storing relations and able to perform basic relational...
Words: 18417 - Pages: 74
...and accurate. * Integrity Constraints: In the database rules that the data must follow. * Redundancy: When unnecessary duplicate of data is in the database. * Data Independence: The form of databse management that keeps the data separate from programs that use the data. Answer the following: 1. What is the relationship between an entity and an attribute? The data related to person, object, or event. 2. What is a database? A structure that can store information about multiple types of entities. 3. What are the advantages and disadvantages of a database (provide 3 each)? Advantages are controlling redundancy, sharing data, and expanding security. Disadvantages are larger file size, increased complexity. 4. What is relational algebra? Identify four different commands and their purpose. A formal system for manipulating relations. Four different commands are Selection whose purpose is to pick rows according to a certain criteria. Projection’s purpose is to pick columns to output. Cross product’s purpose is to pair rows from two tables. Renaming which rename a table and or it’s...
Words: 253 - Pages: 2
...Two- Week ISTE Workshop for teachers on ‘Database Management Systems’ (21st – 31st May, 2013) Course Coordinator: Prof. S. Sudarshan Day / Date Tue 21 May 2013 09:00 – 10:30 Inaugural remarks (15 mins) Relational Model, SQL Part 1: Relations and Relational Algebra, Basic SQL, Joins, Set operations (Chapters 1, 2 and 3) SQL Part 2: Aggregate functions, Nested Subqueries, Database modification (Chapters 3 and 4) Tea Break 11:00 – 13:00 Session Continues.. (last 30 min discussion/quiz) Lunch 14:00-17:00 Tea Break 5:15-6:00 10:30 – 11:00 13:00 – 14:00 Lab 1: Basic SQL Installing, administering and using PostgreSQL and pgAdmin3; Basic SQL queries Lab 2: Intermediate SQL Aggregation, nested subqueries, database modification 17:0017:15 No Session Wed 22 May 2013 Session 10:30 – Continues.. 11:00 (last 30 min discussion/quiz) 13:00– 14:00 17:0017:15 Linux System Admin. (IITB CSE Sysadms) Thu 23 May 2013 Fri 24 May 2013 Sat 25 May 2013 Sun 26 May 2013 SQL Part 3: Session Outerjoins, Transactions, Integrity 10:30 – Continues.. constraints, Triggers, 11:00 (last 30 min Authorization, JDBC discussion/quiz) (Chapters 4 and 5) ER Design (Chapter 7) Session 10:30 – Continues.. 11:00 (last 30 min discussion/quiz) 10:30 – 11:00 Session Continues.. (last 30 min discussion/quiz) 13:00 – 14:00 Lab 3: Advanced SQL Outerjoins, DDL: integrity constraints, authorization Lab 4: ER Design Tutorial (Last 45 mins for solutions discussion, broadcast)...
Words: 591 - Pages: 3
... Professor Mattos Lithonia Campus CIS 111 Intro to Relation Dbase Mgt System Feb 28, 2013 Strayer University Heuristics We are finally approaching the end of this proposal but before I conclude this research data I would like to tell you about heuristics and how it is used to access your database information. This report explains the implementation of an algorithm to optimize a QT with heuristic optimization rules. These rules were taken from [1] chapter 16 and [2] chapter 11. Heuristic optimization rules are based on properties of operations as mathematical operations in the relational algebra. Summaries of these properties can be found both in [1] and [2] also. These properties give the following heuristic rules for query optimization: 1. Perform SELECT operations as far down the tree as possible. This has the effect of reducing the number of tuples in later binary operations which are highly expensive. 2. Perform PROJECT operations as far down the tree as possible. This has the effect of reducing the number of attributes in each tuple and reduces the memory requirements, attempting to cut down on secondary storage usage. 3. Combine successive SELECT operations into one composite SELECT operation and successive PROJECT operations into one composite PROJECT operation. 4. Combine a PRODUCT followed by a SELECT into a JOIN...
Words: 395 - Pages: 2
...Part 1: You are given the E-R diagram in following Figure. Add the following relationships to this diagram and mark the cardinalities of each relationship clearly. HAVE-ACCOUNT: people have credit card accounts. Each credit card account has to have one or more people who own that account. One person may have none, one or many credit accounts. SPONSOR-ACCOUNT: stores sponsor credit card account. Each store may have none, one or more accounts. Each credit card may be sponsored by at most one store. PUBLISH: stores publish catalogs. Each catalog has to belong to a store and each store may publish at most one catalog. A store may not publish a catalog at all. Part 2: Convert the E-R diagram you constructed in Part 1 into the corresponding relational schema. List all tables. In each table, underline the primary identifier. If a specific attribute is a foreign key to another relation, write a short note indicating this. Use as few tables as possible. Practice Question -03 You are given the diagram following Figure for storing information about a virtual store containing various items from different stores. Convert this entity relationship diagram to...
Words: 1214 - Pages: 5
...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 denoted by the...
Words: 700 - Pages: 3
