Applications of dbms

2/13/2017

Applications of DBMS Database assignment help

De PROGRAMMING ASSIGNMENT HELP

Task 1: Applications of DBMS Assignment Analyse the key issues and application of databases within organizational environments The needs of management and organizations are ever changing, still they are required to be met.

There are external pressures from security agencies, taxing authorities, legislators making privacy laws. Such external and internal factors demand organizations to exercise control over their data sources.

All levels of people in organization query from the database for information to conduct daily operations. Here is a list of factors describing why ‘Click Money’ should use databases to improve its day to day operations – 



         

Middle and top management receives reports comparing actual results and actual results.

Database are vital for day to day decision making, planning, control, operations and the management process.

Management of databases help businesses to keep a track of their clients along with their preferences and buying patterns. E.g. – By looking at the databases we can find out which chocolates are always purchased, which hotels are frequently booked etc.

To maintain client history, To maintain product inventory, To maintain supplier information, To maintain manufacturer details, To maintain staff details, To maintain attendance management, To maintain payment structure, mode of payment, To maintain shipment tracking and all necessary pieces of information are often stored and retrieved from databases.

To automate the financial advice provided to clients To store data of various branches of click money at a centralized location and to access them online

Task 2 General Features of Database Management Systems The main purpose of database management system is to maintain data integrity in the system. But a good database management system has some other features as well – 1. Data Definition Language – These are set of commands used to define database schema.

E.g. –

www.programmingassignmentshelp.net

help@programingassignmentshelp.net

     

RENAME – Renames an object in database ALTER – Alters the structure of the database DELETE – Deletes some or all rows from a table. After clicking this command, we need to commit or rollback transaction to make changes permanent.

DROP – Removes a table from database.

TRUNCATE – removes all records from a table, including all spaces allocated for the records. It is faster than delete command and changes are permanent.

CREATE – It creates objects in database (E.g. : Create table employee)

2. Data Manipulation Language – These commands manage schema definition. E.g.   

SELECT – This command is used to retrieve data from the a database INSERT – This command is used to insert data into a table UPDATE – This command is used to updates existing data within a table DELETE – This command is used to delete all records from a table

3. Data Dictionary and metadata – A data dictionary is a database repository or a store where metadata resides. A metadata is data about data.

A data dictionary contains:   

Description of database users and their access permissions Logical and physical structure and design of database, access paths, storage structures etc.

Schema, database mapping, constraints

4. Indexing of data – It is a technique of retrieving data from database based on a value upon which indexing is done. E.g. – All records with name = ‘A’ must be fetched.

5. Storage of Data – The saving of data within database is data storage.

6. Instances and Schema – Data within the database keeps on changing with time as it is frequently updated, modified, deleted. This information at any particular point of time is called an instance. The overall design of database is called schema. E.g. – There are three fields in table ‘Student’ – Student id, Student Roll No, Student Class. The values within these fields are instances and the type of fields (Integer, varchar, nvarchar) in this table is the schema.

7. Data Abstraction – It is the property of showing necessary details and hiding unwanted details from users. Through several levels of abstraction, developers hide the complexity of the system from end users, as most of the database system users are not computer literate.

Various levels of data abstraction are –   

Physical Level – It explains how data is actually stored in the database.

Logical Level – It explains what data is stored in that database and what relationship exists among those data.

View Level – It describes a part of the database, in which the end user is interested, hiding away other details. Many views for the same database are provided by the system.

The following diagram shows the interdependence among various levels:

www.programmingassignmentshelp.net

help@programingassignmentshelp.net

8. Data Independence – It is the ability to modify database schema at one level without affecting schema of next higher level. It can be divided into – 

Physical Data Independence – To change the physical schema without disturbing schema at logical level. Occasionally necessary to improve system performance.



Logical Data Independence – To change the logical schema without disturbing schema at view level. Necessary when logical structure of database is altered.

Advantages of Database Management Systems: 1. Controlling Data Redundancy-The data is recorded at only one place in a database and is distinct. E.g. – Dean’s faculty file and Faculty payroll file are two sets of data which have multiple similar entries. When these are converted into a database, the data is integrated such that redundancy is removed and only one record is kept.

2. Data Consistency – If a data item appears once in a database, its value must be updated only once and as soon as this is done, it must be made available to users. The database management system automatically updates each occurrence of a data item in a database.

3. Data Sharing – Many users can access same set of data / database simultaneously, as database administrator can give access rights to various users within organisation.

Similarly, same database can be shared by multiple application programs.

4. Data Integration – One database can contain multiple tables with multiple rows and columns and multiple relationships amongst tables.

5. Integrity Constraints-These are some consistency rules applicable to database such that only correct data is entered. E.g. – Maximum marks cannot be greater than 100, ‘Issue date’ of a book in library system cannot be later than ‘Return Date’ etc. Some standard integrity constraints in most database management systems are: Primary Key, Foreign Key, Not Null (specifies that a column cannot contain null values), Check (specifies a condition that each row of a table must satisfy), Unique.

www.programmingassignmentshelp.net

help@programingassignmentshelp.net

6. Data Security – protection of database from unauthorised users. It is ensured by implementing password and permissions. By giving different permissions like, some users have right to insert, update, delete, modify data while others can only view the data and can’t update it. Usually Database Administrators (DBA) have permissions to access all the data of the organisation. He creates the user accounts and gives adequate permission to access database. An example of security can be when we insert card in ATM, it reads ID number on card and asks for pin. In this way we can access our account.

7. Data Atomicity – In commercial database, a transaction is considered as atomic unit of work. E.g. – When we purchase something from a point of sale terminals in shopping malls, a number of tasks are performed like- Sales person’s commission increases, Company’s stock is updated, Money is transferred to company’s account. All these tasks collectively form one transaction. All these tasks must be complete else partial tasks are rolled back.

Thus database management system ensures that only consistent data is stored within database.

Read about Types of Database Models

Relational Algebra Concepts 

Entity – It is a unique object. E.g. – A person a with certain social security number is an entity.



Attribute – It refers to a field in database. E.g. – Roll Number, Registration number are attributes of student table.



Relational keys: There are two kinds of keys in relations. The first are identifying keys: the primary key is the main concept, while two other keys – super key and candidate key – are related concepts. The second kind is the foreign key.



Super Keys: A super key is a set of attributes whose values can be used to uniquely identify a tuple within a relation. A relation may have more than one super key, but it always has at least one: the set of all attributes that make up the relation. E.g. – We take customer master table with following fields : cust_id, cust_name, social security number (SSN), cust_address, cust_dobWe can make two super keys in this example :

Customer Name+SSN+Date of Birth Customer ID+Name+SSN We can further reduce this process. As a ‘customer id’ is unique to each customer. So, we can reduce the super key to just one field, customer id, which is the candidate key. However, a composite candidate key may also be formed by combining customer id with SSN.

www.programmingassignmentshelp.net

help@programingassignmentshelp.net



Primary Key – Uniquely identifies the records stored in a table. E.g. – Zip code.

Sometimes there are situation where more than one city may share the same zip code, in that case we might have to take up some other unique record to be made as primary key.

Normally an id, registration number or roll number are considered as good primary keys.

Relationship between identity keys. The relationship between keys:

Super key ⊇ Candidate Key ⊇ Primary Key 

Candidate Key – A candidate key is a super key that is minimal; that is, there is no proper subset that is itself a super key. A relation may have more than one candidate key, and the different candidate keys may have a different number of attributes. In other words, you should not interpret ‘minimal’ to mean the super key with the fewest attributes.

E.g. – If there is no unique id in the table, then a combination of name, address etc can be made as primary key. 

Foreign Key – Primary key of table a can be a foreign key in table b. It is a column or group of columns that provide cross reference between two tables. E.g. – In order to create a relationship between two tables’s customer and order, both having customer id column.

Customer Id in order table becomes foreign key in order table, which is primary key in customers table.

There are certain referential integrity constraints associated with foreign key – Cascade, set null, restrict 1. Cascade – If rows in parent table are deleted, the matching foreign key rows in child table are also deleted.

2. Set Null – When a row in parent table is deleted or updated, the matching foreign key values are set to null, thus maintaining referential integrity.

3. Restrict – If columns are being referenced as foreign key, we cannot delete the columns of parent table.

The concept of referential integrity constraint is best represented by this example: Database 1: Company A Tables: Employee Master (Emp_id, Emp_name) Employee Salary (Emp_id, Emp_salary) Now if we have to delete an employee record from employee master, it is also referenced in employee salary table. So deleting a record from Employee master will affect other tables associated with it. So it will be quite cumbersome, to do so. ================================================================

Task 3 www.programmingassignmentshelp.net

help@programingassignmentshelp.net

Database Developmental Methodology Structured Systems Analysis and Design method (SSADM) is a set of standard for system analysis and design used for computing projects in United Kingdom.

It uses combination of 3 techniques: 





Logical Data Modelling – It is the process of identifying, modelling, documenting the data (entity & relationships) requirements of the system. Entities are the things about which businesses need to record information and relationships are the associations among entities.

Data Flow Modelling – It is the process of identifying, modelling, documenting the flow of data within information system. It examines the activities that convert data from one form to another, data stores, external entities that send data or receive data from other system, data flows which are the routes by which data moves.

Entity Behaviour Modelling – It is the identification, modelling and documentation of events that affect each entity and the sequence in which these events occur.

All these three system models provide a different viewpoint of whole system which is essential in forming complete model of the system being designed. These system models are cross referenced against each other, thus ensuring the accuracy and completeness of entire application.

It is a method which is used for projecting and analysis of Computer Choice System Requirement to design Database of system.

SSADM specifies the modules, stages and tasks to be carried out, the deliverables to be produced and furthermore the techniques used to produce the deliverables. SSADM adopts the Waterfall model (see diagram below) of systems development, where each phase has to be completed and signed off before subsequent phases can begin.

Waterfall Model

www.programmingassignmentshelp.net

help@programingassignmentshelp.net

SSADM Assumptions:   

Changing business methods processes will need to reflect changes underlying data in the system changes very little

SSADM application projects can be divided into 5 developmental stages – 1. Feasibility Study- It is conducted to determine whether a system can be built within the defined budget or not. It can be divided in two parts – Problem definition and problem identification. Refer Level 0 DFD below.

Requirements Analysis – In this phase the requirements of the system to be developed are identified, with the process and data structures involved. Data Flow Diagrams (DFD) are created. Diagrams of new systems are made including the security requirements of new system.

Supplier supplies products to the company, which is Click Money.We can explain this DFD in a number of simple steps:   

Company (Click Money) has multiple branches across various cities.

Click Money manages the database of all the branches at one centralized location.

Branches in turn manages all their info and provide to Click Money centralized server.

www.programmingassignmentshelp.net

help@programingassignmentshelp.net

   

Click Money has many customers. It sells its financial products to customers and receives service charges from them.

Click Money Database manages all the relevant info of all the customers.

Click Money has staff, whose records are maintained in database server.

Staff salary and Attendance status is to be managed by Click Money Database.

3. Requirements Specification – All the functional and non functional requirements are identified in detail in this phase. And new techniques are introduced to define the required processing and data structures. This stage involves detailing the already defined requirements for the chosen option within the previous stage. It is made in several steps.

This stage has as a result the requirements catalogue and the new system pattern and by the evolution in time of the identified entities within the system.

4. Logical System Specification – In this phase system design is updated logically and technically. Entity Relationship diagrams (ER Diagram) are made. Refer ER Diagram below.

5. System Design – It can be divided into 3 sub stages – 6. Data Design : Involved Data Structures and Database management systems are identified, 7. Process Design: This is the logical design of the system where the system design will have to pass through a quality control test. At this stage another feasibility study can be undertaken and proposed system can be adapted if necessary. This allows to correct any errors before making further progress, 8. Physical design: In this phase database design is set up physically and set of program specifications are created using logical and technical specifications.

9. Structured system analysis and design methodology works best on projects with clearly defined objectives.

Task 4 Entity relationship Diagram for Click Money Database

www.programmingassignmentshelp.net

help@programingassignmentshelp.net

In this E-R diagram we can see that various ‘Suppliers’ supplies to ‘Company’ (Click Money).

‘Company’ has many ‘branches’. Branches can be Head Office, Regional office or Sub Offices.

Company sells various ‘products’ which are supplied by suppliers. ‘Customers’ buys those products. ‘Staff’ works for Company. Company also keeps track of the staff ‘attendance’.

www.programmingassignmentshelp.net

help@programingassignmentshelp.net

Here is a list of the various relationships, entities and corresponding cardinality constraints – Relation Type

Entity Class

Entity Class

Cardinality Ratio

Works for

staff

company

One to many

sell

staff

product

One to many

purchase

customer

product

One to many

has

company

customer

One to many

Has

supplier

manufacturer

One to many

Consider the above entity relationship diagram, we find that there are entities with attributes and relationships with cardinality constraints defined in this diagram.

Various Entities, Attributes, Constraints that we see in this E-R Diagram are –

1

Entity

Attributes

Constraints

Supplier

Supp_id

Primary Key

Supp_fname

Not Null

Supp_fname

Not Null

Supp_phone

Not Null

Product_id

Foreign key

www.programmingassignmentshelp.net

help@programingassignmentshelp.net

2

3

Company

Branch

Supp_Street

Not Null

Supp_City

Not Null

Comp_id

Primary Key

Comp_fname

Not Null

Comp_lname

Not Null

Comp_phone

Not Null

Comp_street

Not Null

Comp_city

Not Null

Branch_id

Primary Key

Branch_fname

Not Null

Branch_lname

Not Null

Branch_phone

Not Null

Branch_street

Not Null

Branch_city

Not Null

www.programmingassignmentshelp.net

help@programingassignmentshelp.net

4

5

6

Product

Staff

Attendance (Weak entity set)

Prod_id

Primary Key

Prod_name

Not Null

Supplier_id

Foreign Key

Staff_id

Primary Key

Staff_fname

Not Null

Staff_fname

Not Null

Staff_salary

Not Null

Staff_phone

Not Null

Staff_street

Not Null

Staff_city

Not Null

Staff_id

Staff_fname

Primary Key

Staff_lname

Status

www.programmingassignmentshelp.net

Not Null

help@programingassignmentshelp.net

Various relationships that exist in the above Entity Relationship diagram for Click Money are: Relation Type

Entity Class

Entity Class

Cardinality Ratio

Supplies

supplier

Company

Many to one

Has

company

Branches

One to many

Sells

company

Product

One to many

buys

customer

Product

One to many

Works for

staff

Company

Many to one

Keep track

company

Attendance

One to one

Here cardinality is essentially the mapping of associated entities in the relationship.

Types of cardinality are: One to one (1:1) – E.g. – An employee works for a company, there exists a unique employee and unique company One to many (1: n) – E.g. – One Staff member sells many products Many to One (n: 1) – E.g. – Manufacturer supplies many products to one supplier Many to many (m: n) – E.g. – Many projects can be assigned to many employees Normalization – This schema is normalized up to 3NF (Third Normal Form). Each cells of table have single value of attribute. Hence it is following 1 NF, there is no partial or transitive dependency. Every relation is uniquely defined, hence it is also following 2NF and 3 NF.

Task 5 Relational Data Model Tables with corresponding attributes and constraints –

www.programmingassignmentshelp.net

help@programingassignmentshelp.net

1. Supplier – Supp_id (PK), Supp_fname, Supp_lname Supp_phone, Product_id (FK), Supp_street, Supp_city 2. Branch – Branch_id (PK), Branch_fname, Branch_lname, Branch_phone, Branch_street, Branch_city, Company_id (FK) 3. Product – Product_id (PK), Product_name, Supplier_id (FK) 4. Customer – Cust_id (PK), Cust_fname, Cust_lname, Cust_phone, Cust_street, Cust_city 5. Staff – Staff_id (PK), Staff_fname, Staff_lname, Staff_salary, Staff_phone, Staff_street, Staff_city 6. Attendance – Staff_id, Staff_fname, Staff_lname, Status (In this table, two candidate keys i.e Staff_id + Staff_fname + Staff_lname are together used to form a primary key) 7. Company – Comp_id(PK), Comp_fname, Comp_lname, Comp_phone, Comp_street, Comp_city Above database schema shows all the tables of Click Money database with their primary and foreign keys.

Click Money Database screenshot using MS Access:

Above screenshot shows a table relationship diagram for Click Money Database in MS Access.

www.programmingassignmentshelp.net

help@programingassignmentshelp.net

Above diagram shows the Customer Details interface of Click Money. By clicking the button we can navigate across the records of database.

Normalization: There is no partial or transitive dependency, every relation is uniquely defined. Relations are having single attribute for a key. Hence the database schema is following 1NF, 2NF, 3NF. ===========================================================

Task 6 Please refer MS access database, which shows following things: 1. The necessary constraints, and the appropriate relationships 2. Database tables with sample records 3. A functioning front end user interface that will suit client’s requirements. E.g. – Forms are created wherever necessary.

Read more about Computer Programming Assignments Help

References: 1. Date, C.J., Introduction to Database Systems (7th Edition) Addison Wesley, 2000 2. Abraham Silberschatz, Henry F. Korth, Sudarshan, Database Systems Concepts, McGraw-Hill Higher Education, 2006 3. http://www.htmlgoodies.com/primers/database/article.php/3478121

www.programmingassignmentshelp.net

help@programingassignmentshelp.net

4. http://www.teachict.com/as_as_computing/ocr/H447/F453/3_3_9/ddl/miniweb/pg2.htm 5. http://www.tutorialspoint.com/dbms/dbms_indexing.htm 6. http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_comma nds 7. http://www.studytonight.com/dbms/database-normalization.php Programming Assignments Help is the best assignment help provider in the United Kingdom.

Our online assignment writing help UK is especially dedicated for the students studying in all UK colleges and universities. Submit assignment to get the best quality assignment help.

www.programmingassignmentshelp.net

help@programingassignmentshelp.net