Free Essay

Dbms

In: Computers and Technology

Submitted By ankita2015
Words 648
Pages 3
Faculty of Science and Technology
ITECH1006/5006
Database Management Systems
Database Management Systems
Tutorial Week 3
Tasks
1. Given the following table, convert the table into normalised data structure showing all attributes and identifying primary keys. Show your normalisation process from the un-normalised form to the third normal form.

2. Given the following table, convert the above table into normalized data structures showing all attributes and identifying primary keys. Show your normalisation process from the un-normalised form to the third normal form.
Customer
Number
148

148

282
356
356
408

608

608

608

Customer
Name
AI’s
Appliance
and Sport
AI’s
Appliance and Sport
Brookings
Direct
Ferguson’s
Ferguson’s
The
Everything
Shop
Johnson’s
Department
Store
Johnson’s
Department
Store
Johnson’s
Department
Store

CRICOS Provider No. 00103D

Order
Number
21608

Order Date

Part
Description
Iron

Number
Ordered
11

Quoted
Price
$21.95

Warehouse

10/20/2007

Part
Number
AT94

3

Rep
Number
20

21619

10/23/2007

DR93

Gas Range

1

$495.00

2

20

21614

10/21/2007

KT03

Dishwasher

2

$595.00

3

35

21610
21610
21613

10/20/2007
10/20/2007
10/21/2007

DR93
DW11
KL62

Gas Range
Washer
Dryer

1
1
4

$495.00
$399.99
$329.95

2
3
1

65
65
35

21617

10/21/2007

BV06

Howe Gym

2

$794.95

2

65

21617

10/21/2007

CD52

Microware
Oven

4

$150.00

1

65

21623

10/21/2007

KV29

Threadmill

2

$1,290.00

2

65

Page 1 of 3

3. Given the sales data in the following table, normalise the table to the third normal form by considering the business rules stated in each of the cases stated below.
Date

Product

Price

Client

Phone

Address

11 Jan

Widget

100

Nurk Inc.

666-999

11 Bush Ave

12 Jan

Gizmo

120

Klutz & Co

131-313

13 Luck Rd

12 Jan

Widget

100

Bloggs Ltd

123-456

12 High St

13 Jan

Widget

100

Klutz Coy.

131-323

13 Luck Rd

14 Jan

Gizmo

120

F. Nurk Inc.

666-999

11 Bushy Ave

Case 1:
 Each client makes no more than one order per day
 No two clients have the same name
 Each order consists of a single product
Case 2:
Assuming that in any single day,
 Clients sometimes make several orders
 Each order is for a different product
Case 3:
Assuming that that clients sometimes can order the same product several times in a single day.
4. Given the following table and the following business documentation:



Each employee belongs to one department only.
An employee is assigned to at least one project and a project must have at least one employee. a) Convert the table into normalized data structures showing all attributes and identifying primary keys.
b) Present your normalized relations as an ER diagram.
Employee No: 01267
Employee Name: Graham Clark
Employee Address: 12 Smith Street Richmond VIC 3456
Hire Date: 02/03/1980
Salary: 30,000
Project(s) assigned to:
Project no:
Project Start Date:
Marketing project
123
12/01/2002
Payroll project
175
13/06/2002
Leave: 03/03/1999 - 17/03/1999
15/12/1999 - 29/12/1999
Department No: 05
Department Name:
Department Location: MEL
Computing

CRICOS Provider No. 00103D

Page 2 of 3

5. For the following relations, determine what normal form each is in:
a) ORDER_LINE(OrderNumber, ItemNumber, ItemDescription, Price, Qty)
b) STORE_MERCHANDISE (SKU, PromotionID, Seller, Style, Price)
Given that the following dependencies exist:
SKU, PromotionID determines Seller, Style, Price
SKU
determines
Seller, Style
c) Convert the following relation to 3NF; the repeating data within the relation has been identified using {}:
PATIENT(HouseholdNum, HouseholdName, Street, City, State, PostCode, Balance,
PatientNum, PatientName, {ServiceCode, Description, Fee, Date})
The following dependencies within the data:
PatientNum -> HouseholdNum, HouseholdName, Street, City, State, PostCode, Balance,
PatientNum, PatientName
HouseholdNum -> HouseholdName, Street, City, State, PostCode, Balance
ServiceCode -> Description, Fee
PatientNum, ServiceCode -> Date

CRICOS Provider No. 00103D

Page 3 of 3…...

Similar Documents

Premium Essay

Dbms

...recipient have, and public-key systems that use two keys, a public key known to everyone and a private key that only the recipient of messages uses. Cryptography is used to protect e-mail messages, credit card information, and corporate data. One of the most popular cryptography systems used on the Internet is Pretty Good Privacy because it's effective and free. Section C: Long Questions (20 marks) 1. Distinguish between centralized and distributed systems ANS: While a centralized database keeps its data in storage devices that are in a single location connected to a single CPU, a distributed database system keeps its data in storage devices that are possibly located in different geographical locations and managed using a central DBMS. A centralized database is easier to maintain and keep updated since all the data are stored in a single location. Furthermore, it is easier to maintain data integrity and avoid the requirement for data duplication. But, all the requests coming to access data are processed by a single entity such as a single mainframe, and therefore it could easily become a bottleneck. But with distributed databases, this bottleneck can be avoided since the databases are parallelized making the load balanced between several servers. But keeping the data up to date in distributed database system requires additional work, therefore increases the cost of maintenance and complexity and also requires additional software for this purpose. A......

Words: 2698 - Pages: 11

Premium Essay

Dbms (Obms & Oracle 91)

...MC0067 - 01 Marks - 40 DBMS (OBMS & ORACLE 91) 1. Write a note on File based system/ Data base system Solution: File-Based Systems: Conventionally, Before the database Systems evolved , data in the software’s Systems was stored in and represented using flats files. Drawbacks of File-Based Systems: File - Based Systems [pic] As shown in the figure, in a file-based system, different programs in the same application may be interacting with different private data files. There is no system enforcing any standardized control on the organization and structure of these data files. • Data Redundancy and Inconsistency Since data resides in different private data files, there are chances of redundancy and resulting inconsistency. For example, in the above example shown, the same customer can have a savings account as well as a mortgage loan. Here the customer details may be duplicated since the programs for the two functions store their corresponding data in two different data files. This gives rr.>e to redundancy in the customer's data. Since the same data is stored in two files, inconsistency arises if a change made in the data in one file is not reflected in the other. • Unanticipated Queries In a file-based system, handling sudden/ad-hoc queries can be difficult, since it requires changes in the existing programs. • Data Isolation Though data used by different programs in the application may be related, they reside in isolated data files. ...

Words: 4599 - Pages: 19

Premium Essay

Dbms

...______________________________________ ------------------------------------------------- Roll No. : ______________________________________ ------------------------------------------------- ------------------------------------------------- An Introduction to Database Management Systems A database is a collection of related files that are usually integrated, linked or cross-referenced to one another. The advantage of a database is that data and records contained in different files can be easily organized and retrieved using specialized database management software called a database management system (DBMS) or database manager. DBMS Fundamentals A database management system is a set of software programs that allows users to create, edit and update data in database files, and store and retrieve data from those database files. Data in a database can be added, deleted, changed, sorted or searched all using a DBMS. If you were an employee in a large organization, the information about you would likely be stored in different files that are linked together. One file about you would pertain to your skills and abilities, another file to your income tax status, another to your home and office address and telephone number, and another to your annual performance ratings. By cross-referencing these files, someone could change a person's address in one file and it would automatically be reflected in all the other files. DBMSs are commonly used to manage: * Membership and......

Words: 5712 - Pages: 23

Premium Essay

Dbms

...transaction recovery and media recovery. 9. Media recovery deals with disk error. 10. The task of a DBMS is quite complex. END OF SECTION A 2 IIBM Institute of Business Management Examination Paper of Database Management Systems Section B: Short Questions (20 marks)     This section consists of short questions. Answer should be in 5 lines. Each Question carries 5 marks. Attempt any four questions. 1. What is SQL? Why is it a powerful language? 2. What are the various transaction models? 3. Discuss the principles of Security? 4. What is Cryptography? 5. What is Semi-Join? END OF SECTION B Section C: Long Questions (20 marks)  This section consists of Long Questions.(word limit 150-200 words)  Each question carries 10 marks.  Attempt any two questions. 1. Distinguish between centralized and distributed systems. 2. Explain the idea of abstractions. 3. What is sequential organization? What are its advantages and disadvantages? END OF SECTION C Section D: Very Long Question (40 marks)     This section consists of very long question. Each question carries 20 marks. Attempt any two questions. Detailed information should form the part of your answer (word limit 200-250 words). 3 IIBM Institute of Business Management Examination Paper of Database Management Systems 1. Discuss database explaining why it is required? List the different types of DBMS model. 2. Discuss various anomalies in databases. How would you explain good database designing?......

Words: 643 - Pages: 3

Premium Essay

Dbms

... Lovely Professional University Case study on student record keeping system Name Abhishek Bhatt Regd. No. 11109390 Roll No. A21 Section K1104 Submittked By Submitted to Abhishek Bhatt Vipin Kumar Case Study case study on student record keeping system Table Of Content 1) Introduction 2) What is DBMS ? 3) Features/Advantages of DBMS. 4)Disadvantages of DBMS 5)Advantages of student record keeping system 6)Note * Introduction : * DBMS : DBMS Stands for Data Base Management System. It consists of interrelated data and set of programs to access those data. * Data : Raw information is called data. * Database : Database is a collection of interrelated data, contain information about one particular enterprise. * Interrelated Data : It is a type of data which is related to each other. e.g. Student and subject, parents and child. * About My Topic : My topic is about case study on student record keeping system * Introduction to case student record keeping system Student Record Keeping System is a comprehensive solution for all of a school’s student management needs,......

Words: 1384 - Pages: 6

Free Essay

Dbms Class

...appreciate the concepts described here, but should be able to do so by the end of the course. The specifics of the E-R, relational, and object-oriented models are covered in later chapters. These models can be used in Chapter 1 to reinforce the concept of abstraction, with syntactic details deferred to later in the course. If students have already had a course in operating systems, it is worthwhile to point out how the OS and DBMS are related. It is useful also to differentiate between concurrency as it is taught in operating systems courses (with an orientation towards files, processes, and physical resources) and database concurrency control (with an orientation towards granularity finer than the file level, recoverable transactions, and resources accessed associatively rather than physically). If students are familiar with a particular operating system, that OS’s approach to concurrent file access may be used for illustration. 3 4 Chapter 1 Introduction Exercises 1.1 List four significant differences between a file-processing system and a DBMS. Answer: Some main differences between a database management system and a file-processing system are: • Both systems contain a collection of data and a set of programs which access that data. A database management system coordinates both the physical and the logical access to the data, whereas a file-processing system coordinates only the physical access. • A database management system reduces the amount of data duplication......

Words: 41091 - Pages: 165

Premium Essay

Dbms

...include code for the metadata of each file - Each application program must have its own processing routines for reading, inserting, updating, and deleting data - Lack of coordination and central control - Non-standard file formats Problems with Data Redundancy - Waste of space to have duplicate data - Causes more maintenance headaches The biggest problem: - Data changes in one file could cause inconsistencies - Compromises in data integrity SOLUTION: The DATABASE Approach - Central repository of shared data - Data is managed by a controlling agent - Stored in a standardized, convenient form Database Management System - A software system that is used to create, maintain, and provide controlled access to user databases - DBMS manages data resources like an operating system manages hardware resources Advantages of the Database Approach - Program-data independence - Planned data redundancy - Improved data consistency - Improved data sharing - Increased application development productivity - Enforcement of standards - Improved data quality - Improved data accessibility and responsiveness - Reduced program maintenance - Improved decision support Costs and Risks of the Database Approach - New, specialized personnel - Installation and management cost and complexity - Conversion costs - Need for explicit backup and recovery - Organizational conflict Elements of the Database Approach Data models - Graphical system capturing nature and......

Words: 391 - Pages: 2

Free Essay

Dbms

...which transaction may pass are as follows: 1. Active state: the initial state, the transaction stays in this state while it is executing. 2. Partially committed: a database transaction enters this phase when its final statement has been executed. At this phase, the database transaction has finished its execution, but it is still possible for the transaction to be aborted because the output from the execution may remain residing temporarily in main memory - an event like hardware failure may erase the output. 3. Failed state: A database transaction enters the failed state when its normal execution can no longer proceed due to hardware or program errors). 4. Aborted state: A database transaction, if determined by the DBMS to have failed, enters the aborted state. An aborted transaction must have no effect on the database, and thus any changes it made to the database have to be undone, or in technical terms, rolled back. The database will return to its consistent state when the aborted transaction has been rolled back. The DBMS's recovery scheme is responsible to manage transaction aborts. 5. Committed state: : A database transaction enters the committed state when enough information has been written to disk after completing its execution with success. In this state, so much information has been written to disk that the effects produced by the transaction cannot be undone via aborting; even when a system failure occurs, the changes made by the......

Words: 2607 - Pages: 11

Premium Essay

Dbms Function

...1. While users were updating the Premiere Products database, one of the transactions was interrupted. You need to explain to management what steps the DBMS will take to correct the database. Using the sample log in Figure 7-1 1, list and describe the updates that the DBMS will roll back if transaction 2 is interrupted at 8:10. In transaction 2, the DBMS started it at 8:01, added an Orders table record at 8:02, and added an Order Line table record at8:05 and added an Order Line table record at 8:09. To roll back the transaction, the DBMS deletes the three added records. 2. Occasionally, users at Premiere Products obtain incorrect results when they run queries that include built-in (aggregate, summary, or statistical) functions. The DBA told management that unrepeatable reads caused the problems. Use books, articles, and/or the Internet to research the unrepeatable read problem. Write a short report that explains the unrepeatable-read problem to management and use an example with y our explanation. (Unrepeatable reads are also called inconsistent retrievals, dirty reads, and inconsistent reads.) Unrepeatable-Read Problems occur many times when a different result is seen from the same query while still in progress. This usually happens when dealing with the UPDATE command by other transactions. An unrepeatable read (also called an inconsistent retrieval, dirty read, or inconsistent read) occurs when a user’s retrieval transaction reads a set of data that other users’......

Words: 965 - Pages: 4

Premium Essay

Dbms

...Notes on DBMS Internals Neil Conway neilc@samurai.com November 10, 2003 Preamble These notes were originally written for my own use while taking CISC-432, a course in DBMS design and implementation at Queen’s University. The textbook used by that class is Database Management Systems, 3rd Edition by Raghu Ramakrishnan and Johannes Gehkre; some of the material below may be specific to that text. This document is provided in the hope that it is useful, but I can’t provide any assurance that any information it contains is in any way accurate or complete. Corrections or additions are welcome. Distribution Terms: This document is released into the public domain. Query Evaluation External Sorting • A DBMS frequently needs to sort data (e.g. for a merge-join, ORDER BY, GROUP BY, etc.) that exceeds the amount of main memory available. In order to do this, an external sort algorithm is used. • 2-Way External Merge Sort: – In the first pass, each page of the input relation is read into memory, sorted, and written out to disk. This creates N runs of 1 page each. – In each successive pass, each run is read into memory and merged with another run, then written out to disk. Since the number of runs is halved with every pass, this requires log2 N passes. Since an additional initial pass is required and each pass requires 2N I/Os, the total cost is: 2N( log2 N + 1) – Thus, we can see that the number of passes we need to make is critical to the overall performance of the sort (since in...

Words: 12979 - Pages: 52

Premium Essay

Dbms

...DBMS vs. RDBMS • Relationship among tables is maintained in a RDBMS whereas this not the case DBMS as it is used to manage the database. • DBMS accepts the ‘flat file’ data that means there is no relation among different data whereas RDBMS does not accepts this type of design. • DBMS is used for simpler business applications whereas RDBMS is used for more complex applications. • Although the foreign key concept is supported by both DBMS and RDBMS but its only RDBMS that enforces the rules. • RDBMS solution is required by large sets of data whereas small sets of data can be managed by DBMS. 1. What is database? A database is a collection of information that is organized. So that it can easily be accessed, managed, and updated.   2. What is DBMS? DBMS stands for Database Management System. It is a collection of programs that enables user to create and maintain a database.   3. What is a Database system? The database and DBMS software together is called as Database system.   4.   What are the advantages of DBMS? I.  Redundancy is controlled. II.  Providing multiple user interfaces. III. Providing backup and recovery IV. Unauthorized access is restricted. V.  Enforcing integrity constraints.   5. What is normalization? It is a process of analysing the given relation schemas based on their Functional Dependencies (FDs) and primary key to achieve the properties (1).Minimizing redundancy, (2). Minimizing insertion, deletion and update anomalies.   6. What...

Words: 3541 - Pages: 15

Free Essay

Comparing Dbms

...PT2520: Database Concepts Week 4 Essay: Comparing DBMS ITT Technical Institute – Westminster Walter Gonzales 7/12/15 Comparing DBMS What is database management system (DBMS)? Database management system is reliable mean to organize date into a single location that can be searched and updated at any time. By adding all the information in one location or in this case a server you are storing the information for later use. You can than search any information you require and update it or even remove old items from the database. Today there are several different options or providers for a DBMS server. Among the top providers you have Oracle, MySQL, IBM DB, MS SQL Server. Oracle 12C Oracle 12C is a cloud base database management system. It supports the following operating systems (OS) Microsoft Windows, Linux, Oracle Solaris and some Unix. It is a web-based interface that stores all the procedures on the Oracle Management Repository. The most recent update was released on September 30, 2014. Some of the benefits of Oracle 12c are the following. Per oracle documentation “It provided a secure multitenant application by adding a layer of abstraction or containerization. It allows you to use the cloud providers for an easy and quick mean to allocate and manage the database across multiple systems and data centers without changing the application”. It also provides a disaster recovery, backup, patching, cloning and upgrading flexibility. Oracle is able to replace or move any......

Words: 926 - Pages: 4

Premium Essay

Dbms

...of the real world and is used for specific purposes by one or more groups of users. A DBMS is a generalized software package for implementing and maintaining a computerized database. The database and software together form a database system. We identified several characteristics that distinguish the database approach from traditional fileprocessing applications, and we discussed the main categories of database users, or the actors on the scene.We noted that in addition to database users, there are several categories of support personnel, or workers behind the scene, in a database environment. We presented a list of capabilities that should be provided by the DBMS software to the DBA, database designers, and end users to help them design, administer, and use a database. Then we gave a brief historical perspective on the evolution of database applications.We pointed out the marriage of database technology with information retrieval technology, which will play an important role due to the popularity of the Web. Finally, we discussed the overhead costs of using a DBMS and discussed some situations in which it may not be advantageous to use one. In this chapter we defined a database as a collection of related data, where data means recorded facts. A typical database represents some aspect of the real world and is used for specific purposes by one or more groups of users. A DBMS is a generalized software package for implementing and maintaining a computerized......

Words: 419 - Pages: 2

Premium Essay

Dbms

... * 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. * Less redundancy − DBMS follows the rules of normalization, which splits a relation when any of its attributes is having redundancy in values. * Consistency − 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. * 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. * Multiuser and Concurrent Access − DBMS supports multi-user environment and allows them to access and manipulate data in parallel. * 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. * Security − DBMS offers methods to impose......

Words: 1010 - Pages: 5

Premium Essay

Dbms

...entities, both the degree and name of the relationship need to be specified. In ER diagram, the commonly used symbols are: [pic] Fig. 3.1.1E-R-D Symbols 3.2 E-R Diagram An entity-relationship diagram is a data modeling technique that creates a graphical representation of the entities, and the relationships between entities, within an information system. The E-R Diagram of the proposed attendance management system is: Fig. 3.2.1 E-R Diagram of Attendance Management System 3.3 Schema A database schema of a database system is its structure described in a formal language supported by the database management system (DBMS) and refers to the organization of data as a blueprint of how a database is constructed (divided into database tables in case of Relational Databases). The formal definition of database schema is a set of formulas (sentences) called integrity constraints imposed on a database.  The schema of the attendance management system is: Figure 3.3.1 Schema of the Attendance Management System 3.4 Tabular Representation Of Schema Table 3.4.1 |Entity |Attribute |Constraints |Referential Integrity | |Teacher |TName |Character | | | |Teacher_ID |varchar......

Words: 2569 - Pages: 11