Premium Essay

Database Theory

In: Computers and Technology

Submitted By contactsainow
Words 259
Pages 2
Introduction to the Join

1. List all customers by ID and name along with all the ORDID’s for the orders that those customers have made.
Answer:
SELECT CUSTOMER.CUSTOMER_ID, CUSTOMER.NAME, ITEM.ORDER_ID
FROM CUSTOMER INNER JOIN ITEM ON CUSTOMER.CUSTOMER_ID = ITEM.ORDER_ID 2. For each product, list the PRODID, description, standard price and start date.
Answer:

SELECT PRODUCT.PRODUCT_ID, PRODUCT.DESCRIPTION, PRICE.LIST_PRICE, PRICE.START_DATE
FROM PRODUCT INNER JOIN PRICE ON PRODUCT.PRODUCT_ID = PRICE.PRODUCT_ID;

3. For each product, list the PRODID, description, standard price and start date for current prices only. Hint: the end date will be NULL.
Answer:

SELECT PRODUCT.PRODUCT_ID, PRODUCT.DESCRIPTION, PRICE.LIST_PRICE, PRICE.START_DATE, PRICE.END_DATE
FROM PRODUCT INNER JOIN PRICE ON PRODUCT.PRODUCT_ID = PRICE.PRODUCT_ID
WHERE (((PRICE.END_DATE)=Null));

4. For each item sold, list the ORDID, ITEMID, PRODID and description.
Answer:

SELECT ITEM.ORDER_ID, ITEM.ITEM_ID, PRODUCT.PRODUCT_ID, PRODUCT.DESCRIPTION
FROM PRODUCT INNER JOIN ITEM ON PRODUCT.PRODUCT_ID = ITEM.PRODUCT_ID;

5. The Marketing department wants to know which customers are buying which products. List each customer (CUSTID and name) and the products purchased by that customer (PRODID and description). Products purchased by a given customer should be listed only once. SELECT CUSTOMER.CUSTOMER_ID, CUSTOMER.NAME, PRODUCT.PRODUCT_ID, PRODUCT.DESCRIPTION FROM CUSTOMER INNER JOIN PRODUCT ON CUSTOMER.CUSTOMER_ID = PRODUCT.PRODUCT_ID;

6. List the name of those customers who paid no more than 10% over the minimum price (at the time of purchase) of a product. Include in the listing, the product id, description, actual price, minimum price and order date.

Similar Documents

Premium Essay

Database Theories

...Modern Database Applications | [Type the document title] | | | Contents 1. Gray, J. (2009). Jim Gray on eScience: A transformed scientific method. The Fourth Paradigm: Data-intensive scientific discovery 2 2. Rowley, J. (2007). Wisdom hierarchy: Representations of the DIKW hierarchy. Journal of Information Science 3 3. Goldman, N. (2013). Towards practical, high-capacity, low-maintenance information storage in synthesized DNA. 4 4. Gray, J. (1981). The transaction concept: virtues and limitations. In: VLDB '81: Proceedings of the seventh international conference on Very Large Data Bases 5 5. Codd, E. F. (1970). A Relational Model of Data for Large Shared Data Banks. Communications of the ACM 7 6. Chen, P. (1976). The entity-relationship model: Toward a unified view of data. ACM Transactions on Database Systems 8 1. Gray, J. (2009). Jim Gray on eScience: A transformed scientific method. The Fourth Paradigm: Data-intensive scientific discovery Gray states that there is need to distinguish data-intensive science from computational science; he defines an emerging fourth paradigm for scientific exploration. This paradigm is derived from the deluge of data being produced within scientific research fields, and the necessity for tools which can be utilised within the whole research cycle; data capture, curation, analysis and visualisation. He identified that currently the data being produced is not being organised, or published in a systematic...

Words: 3050 - Pages: 13

Premium Essay

Database Theory

...Chapter 1 Database Systems Discussion Focus How often have your students heard that “you have only one chance to make a good first impression?” That’s why it’s so important to sell the importance of databases and the desirability of good database design during the first class session. Start by showing your students that they interact with databases on a daily basis. For example, how many of them have bought anything using a credit card during the past day, week, month, or year? None of those transactions would be possible without a database. How many have shipped a document or a package via an overnight service or via certified or registered mail? How many have checked course catalogs and class schedules online? And surely all of your students registered for your class? Did anybody use a web search engine to look for – and find – information about almost anything? This point is easy to make: Databases are important because we depend on their existence to perform countless transactions and to provide information. If you are teaching in a classroom equipped with computers, give some “live” performances. For example, you can use the web to look up a few insurance quotes or compare car prices and models. Incidentally, this is a good place to make the very important distinction between data and information. In short, spend some time discussing the points made in Section 1.1, “Data vs. Information.” After demonstrating that modern daily life is almost inconceivable...

Words: 4647 - Pages: 19

Premium Essay

Database Theory Exam 1

... correct: a 5 Accurate, relevant, and timely ________ is the key to good decision making. a. relationships b. data c. processing d. information correct: d 6 What is the name for data about data? a. unique data b. superdata c. metadata d. raw data correct: c 7 Data management is a discipline that focuses on ________. a. the proper generation, storage, and retrieval of data b. the help items to be used by end users c. the management of end users d. the proper generation, storage, and retrieval of information correct: a 8 What is a benefit of using a DBMS? a. It provides seamless Internet access to database data. b. It creates automatic backups. c. It helps create an environment for end users to have access to more data. d. It provides full security to data using private/public key encryption. correct: c 9 The ________ manages interaction between the end user and the database. a. DBMS b. DP c. DM query engine d. DBMQ correct: a 10 The ________ receives all application requests and translates them into the complex operations required to fulfill requests. a. workgroup b. DP c. query d. DBMS correct: d An ad hoc query is a ________. a. spur of the moment question b. pre-planned question c. pre-scheduled question d. question that will not...

Words: 8796 - Pages: 36

Premium Essay

Database Theory Tutorial 1

...DCS5078 DATABASE SYSTEMS TUTORIAL 1 CHAPTER 1 1. Discuss each of the following terms: a. Data b. Field c. Record d. File 2. What is data redundancy, and which characteristics of the file system can lead to it? 3. What is a DBMS, and what are its functions? 4. What is a business rule, and what is its purpose in data modeling? Given the file structure shown in Figure T1.1, answer Problems 5 - 8. FIGURE T1.1 The File Structure for Problems 5-8 5. How many records does the file contain, and how many fields are there per record? 6. What problem would you encounter if you wanted to produce a listing by city? How would you solve this problem by altering the file structure? 7. If you wanted to produce a listing of the file contents by last name, area code, city, state, or zip code, how would you alter the file structure? 8. What data redundancies do you detect, and how could these redundancies lead to anomalies? FIGURE T1.5 The File Structure for Problems 9-11 9. Identify and discuss the serious data redundancy problems exhibited by the file structure shown in Figure T1.5. CDP OCT 2014/2015 1 DCS5078 DATABASE SYSTEMS TUTORIAL 1 10. Identify the different data sources in the file you examined in Problem 9. 11. Given your answer to Problem 10, what new files should you create to help eliminate the data redundancies found in the file shown in Figure T1.5? Use the contents of Figure T1.11 to work problems 12. FIGURE T1.11 Linking Relational...

Words: 355 - Pages: 2

Free Essay

Database Concepts Research Assignment

...name can be uniquely determined from their SSN. However, the reverse statement (name -> SSN) is not true because more than one employee can have the same name but different SSNs.” Transitive dependency occurs when there is an indirect relationship that causes a functional dependency. While looking this up on the internet, it is hard to find a good example but in time I did. The example comes from http://databases.about.com/od/specificproducts/g/transitive-dependency.htm and from the website the example is and I quote “”A -> C” is a transitive dependency when it is true only because both “A -> B” and “B -> C” are true.” Demoralization is a strategy that database managers use to increase performance in their database. This is used to reduce certain types of problems that might come along with database queries that combine data from different tables into a single...

Words: 267 - Pages: 2

Premium Essay

Database

...Business Rules and Data Models DeMone Wallace Professor Carmen Flores CIS 111 July 25, 2015 Databases are very helpful because they contain and organize content on many different topics. Perhaps the three most important uses of creating and using a database would be to store, retrieve, and analyze information. A database can typically be accessed easily, and is usually stored on computers. Although an electronic version such as one created in Microsoft Access may be the first that comes to mind, there are many non-automated database versions as well, such as a personal phone and address book. Though the data and categories may change from business to business, the direct use of the information in a college student database can vary from marketing and advertising to obtaining new student enrollment and creating programs for funding. When collecting information for databases that are used for business purposes, certain laws must be followed that protect consumers to avoid the mishandling of the information. Data collected by college databases serve multiple purposes from marketing and advertising, to government and business reporting. Information that may be commonly useful in this type of database is date of birth, marital status, sex, income, race, location, and veterans/military status. When students are searching for colleges and universities online, student surveys collect demographic information used for tracking enrollment trends. In 2010, Collegetrend.com prompted...

Words: 683 - Pages: 3

Premium Essay

Beez in the Trap

...Can you believe that the authors present this chapter on databases already? We have’t even had time to explore hardware and software to the level that we want or need! ( Now are able to move into a specific application, databases! How exciting, right? I will be asking some time this week what experience everyone has with databases, but we will not be expecting people to create one or work with one. But, first, let’s examine why this is valuable to us and to our organizations. Organizing Data and Information…now that is a challenge for us today given the overwhelming amount of data available today. One could read the title of this chapter and not anticipate that the theory of database design and management would be included. Some authors consider this to be a key element and have clearly indicated the role and influence of database theory and practice is essential. A well-managed organization and IS department can be compared with a well-designed and functioning database. [pic] Source: http://www.datawarehouse4u.info/images/data_warehouse_architecture.jpg The effort this week will not attempt to present the entirety of database analysis, design, maintenance, and management, but will present an overview of it. For those of you who are not experienced or expert in any programming languages, our treatment will be a huge sigh of relief, because we will not create any databases. We will leave that for those other courses. But I would ask that everyone post as part...

Words: 382 - Pages: 2

Premium Essay

About Ip Address

...very rapid transmission of information, and to improve the network will have to improve the construction of the network Ip address database. Because the Internet is based on the Ip address, each Ip has its physical location in theory. In the IP address of the library to do better with sina, Taobao, Tencent, pure, and 17MON and foreign IP library very much, but most famous is maxmind, they are the IP data were good collection management. This paper designed to achieve the independent Ip address library construction, through their own design procedures for the Ip address of the grab, parse, and will meet the requirements of the Ip data saved to the Mysql database. The system data sources are divided into two parts, one part is to grab the spider to grab the data through the program operation, there is a part of the artificial way to collect Ip data. In order to ensure the data coverage and accuracy for the system is operated for a long time, the spiders crawl data capture enough data, and some redundant data from databases are merged in order to improve the query efficiency of the database. This design is based on the use of Maven management projects through the background to build the springmvc framework to achieve the web project. The overall structure of the use of B/S, the server uses the resin4.0 version of the background database using MySQL database. The design uses a spring JPA annotations to realize linkage of the various parts of the, data processing part is using hibernate...

Words: 336 - Pages: 2

Free Essay

Syllabus

...SCHEME OF EXAMINATION FOR MASTER OF COMPUTER APPLICATIONS (MCA) (SIX-SEMESTER Programme) |Semester – I | |Paper |Title of the Paper |Duration |Maximum Marks |Total | |No. | |Of Exam | | | | | | |Theory |Sessional* | | |MCA-101 |Computer Fundamentals and Problem Solving Using C |3 Hours |80 |20 |100 | |MCA-102 |Computer Organisation |3 Hours |80 |20 |100 | |MCA-103 |Discrete Mathematical Structures |3 Hours |80 |20 |100 | |MCA-104 |Software Engineering |3 Hours |80 |20 |100 | |MCA-105 |Computer Oriented Numerical and Statistical Methods |3 Hours |80 |20 |100 | |MCA-106 |Software Laboratory - I |3 Hours | | |100 | | |C (Based on MCA-101) |...

Words: 13848 - Pages: 56

Premium Essay

Sql Fundamentals

...fundamental building blocks of modern database architecture. SQL defines the methods used to create and manipulate relational databases on all major platforms. At first glance, the language may seem intimidating and complex but it's really not all that bad. In a series of articles over the next few weeks we'll explore the inner workings of SQL together. By the time we're through, you'll have the fundamental knowledge you need to go out there and start working with databases! This week, our first article in the SQL series provides an introduction to the basic concepts behind SQL and we'll take a brief look at some of the main commands used to create and modify databases. Throughout this article, please keep our goal in mind: we're trying to get the "big picture" of SQL -- not a mastery of the individual commands. We'll provide a few examples for illustrative purposes and explain the theory behind them, but don't feel frustrated if you can't write your own SQL commands after reading this article. We'll cover each of the major commands in detail in future weekly installments. If you'd like a reminder in your e-mail inbox each week when the next article is posted, please take a moment and subscribe to our newsletter. By the way, the correct pronunciation of SQL is a contentious issue within the database community. In their SQL standard, the American National Standards Institute declared that the official pronunciation is "es queue el." However, many database professionals have taken to...

Words: 1859 - Pages: 8

Premium Essay

Database Design

...real world. A special diagrammatic technique is introduced as a tool for database design. An example of database design and description using the model and the diagrammatic technique is given. Some implications for data integrity, information retrieval, and data manipulation are discussed. The entity-relationship model can be used as a basis for unification of different views of data: t,he network model, the relational model, and the entity set model. Semantic ambiguities in these models are analyzed. Possible ways to derive their views of data from the entity-relationship model are presented. Key Words and Phrases: database design, logical view of data, semantics of data, data models, entity-relationship model, relational model, Data Base Task Group, network model, entity set model, data definition and manipulation, data integrity and consistency CR Categories: 3.50, 3.70, 4.33, 4.34 1. INTRODUCTION The logical view of data has been an important issue in recent years. Three major data models have been proposed: the network model [2, 3, 71, the relational model [S), and the entity set model [25]. These models have their own strengths and weaknesses. The network model provides a more natural view of data by separating entities and relationships (to a certain extent), but its capability to achieve data independence has been challenged [S]. The relational model is based on relational theory and can achieve a high degree of data independence, but it may lose some ...

Words: 9678 - Pages: 39

Premium Essay

Club It Part One

... Chiedo Ugorji InformationSystems and Software Applications 2 - Please remove. Management Information Services A management department uses a computer based system that provides managers with the tools needed to organize, evaluating, and they run their departments more efficiently. They use software that can look at the past, present, andthefuture information. This is calledthe Management Information Service that includes the software to run the databases, the decision support system, and they also have applications that can run the department of the business more efficiently. These systems (management information systems) provide the department employee with the information to run his operations. It also supports the planning, effective resource management; it will enhance customer service, and promote communications. Decision Support Systems Decision Support Systems (DSS) is an interactive computer system that will obtain data from a wide range of sources, for the business purposes. DSS applications are systems that help personal...

Words: 978 - Pages: 4

Premium Essay

Project

...1.0 Introduction. This chapter of the project report comprises of the background of the study, statement of the problem, general and specific objectives of the research, scope of the study and significance of a computerized records management system. 1.1 Background information. I have much concentration on one of primary school known as kyegera parents primary school. It is one of the many privately owned primary schools in kampala district. This primary school was constructed and started operating in 2006 to offer good education to the young generation in the district and perhaps competite with the rest of the country’s schools. The manual based granding system is the current system used by this education institute as I analyze this system I found that the system was costly in terms of purchasing papers, calculator and minimising error. A computerized granding management system for an education institution is all about simpling stundents granding, securing reports, managing and quick retrieval of data and storage of large volume of data with the help of hard drive and can be kept for long period of time. It also allows deletion and editing of data which cannot be done on paper. 1.2 Problem statement. The institutions current system is mainly manual based granding system which are costly in terms of granding using calculator , storage and securing records from intruder to access them. 1.2.1 General objective. The purpose of this project is to design a computerized...

Words: 8272 - Pages: 34

Free Essay

No Sql Databases

...INSY 5337 Data Warehousing – Term Paper NoSQL Databases: An Introduction and Comparison between Dynamo, MongoDB and Cassandra Authored ByNitin Shewale Aditya Kashyap Akshay Vadnere Vivek Adithya Aditya Trilok Abstract Data volumes have been growing exponentially in recent years, this increase in data across all the business domains have played a significant part in the analysis and structuring of data. NoSQL databases are becoming popular as more organizations consider it as a feasible option because of its schema-less structure along with its capability of handling BIG Data. In this paper, we talk about various types of NoSQL databases based on implementation perspective like key store, columnar and document oriented. This research paper covers the consolidated applied interpretation of NoSQL system, depending on the various database features like security, concurrency control, partitioning, replication, Read/Write implementation. We also would draw out comparisons among the popular products and recommend a particular NoSQL solution on the above mentioned factors. 1. Introduction Until recently, Relational database systems have been on the forefront of data storage and management operations. The advent of mobile applications that requires real time analysis like GPS based services, banking and social media has led to huge unstructured data being produced every second. Traditional RDBMS systems have found it difficult to cater to these huge chunks of unstructured...

Words: 4246 - Pages: 17

Premium Essay

Test

...Module 5 Database Management using OpenOffice Base Module Overview Welcome to Module 5 - Database Management using OpenOffice Base. This module explores the building of a database using the OpenOffice 3.0 Base software application. The module is intended to be very hands-on and focus upon two primary areas: the how and why of designing and implementing a database, and to develop a basic understanding of relational database theory. This balance of practice and theory is intended to bring greater design strength and data integrity to your database applications. | Upon completion of this module you will be able to: * Describe a database and how it can be utilised * Discuss the difference between a database and the other applications * Understand how databases are organised and operated * Create a simple database and view database content in various modes * Create a table, define and modify fields and their properties; enter and edit data in a table * Sort and filter a table or form: create, modify and run queries to retrieve specific information from a database * Understand what a form is and create a form to enter, modify and delete records and data in records * Build queries to retrieve and sort data * Create routine reports * Build and print reports for regular and ad hoc reporting of data Terminology | | Database | A database is a collection of related information which is organised into a series of rows (called records) and columns (called...

Words: 20896 - Pages: 84