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