2017
Features of Database Management System
DATABASE ASSIGNMENT HELP
PROGRAMMING ASSIGNMENTS HELP | help@programmingassignmentshelp.net
Task1 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. Database are vital for day to day decision making, planning, control, operations and the management process.
Database management system are designed to manage large amount of information. Examples of large database systems are – Airline reservation systems, banking systems, Automated Teller Machine, Company Payroll System, Staff Management System, Material Management System. Database systems are designed to provide safety of the stored information, from unauthorized access and system crashes. One of the major database property is Database Abstraction. As many of the database users are not computer literate, system ensures to provide only relevant data to them, and hides unnecessary details ( like how data is stored ) from them. This makes functionality of the entire organisation simple and user friendly. Here is a list of four major factors describing why ‘Click Money’ should use databases to improve its day to day operations – 1.
2. 3. 4.
Management of databases help businesses to keep a track of their clients along with their preferences and buying patterns. To automate the financial advice provided to clients. E.g. – By looking at the databases we can find out which chocolates are always purchased, which hotels are frequently booked etc. [Read about Database Key Issues Assignment.] To maintain details of suppliers, manufacturers, staff To maintain product inventory and shipment tracking 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 www.programmingassignmentshelp.net
help@programmingassignmentshelp.net
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. –
RENAME ALTER DELETE DROP TRUNCATE CREATE
2.
Data Manipulation Language – These commands manage schema definition. E.g.-
SELECT INSERT UPDATE DELETE
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 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.
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.
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.
www.programmingassignmentshelp.net
help@programmingassignmentshelp.net
4.
Data Integration –One database can contain multiple tables with multiple rows and columns and multiple relationships among tables.
================================================================
Task 3 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. Data Flow Modelling – It is the process of identifying, modelling, documenting the flow of data within information system. Entity Behaviour Modelling – It is the identification, modelling and documentation of events that affect each entity and the sequence in which these events occur.
These system models are cross referenced against each other, thus ensuring the accuracy and completeness of entire application. SSADM application projects can be divided into 5 developmental stages – 1. 2.
3. 4.
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. 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. We can explain this DFD in a number of simple steps: Supplier supplies products to the company, which is Click Money. 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. 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. Requirements Specification – All the functional and non functional requirements are identified in detail in this phase. Logical System Specification – In this phase system design is updated logically and technically. Refer ER Diagram below.
Task 4 www.programmingassignmentshelp.net
help@programmingassignmentshelp.net
ER Diagram Entity Relationship Diagram for Click Money Database
www.programmingassignmentshelp.net
help@programmingassignmentshelp.net
[This is how to Develop a Database Management System Assignment.] Various Entities, Attributes, Constraints that we see in this E-R Diagram are –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’.
1
2
Entity
Attributes
Constraints
Supplier
Supp_id
Primary Key
Supp_fname
Not Null
Supp_fname
Not Null
Supp_phone
Not Null
Product_id
Foreign key
Supp_Street
Not Null
Supp_City
Not Null
Comp_id
Primary Key
Comp_fname
Not Null
Comp_lname
Not Null
Comp_phone
Not Null
Company
www.programmingassignmentshelp.net
help@programmingassignmentshelp.net
3
4
5
Branch
Product
Staff
www.programmingassignmentshelp.net
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
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
help@programmingassignmentshelp.net
6
Attendance (Weak entity set)
Staff_phone
Not Null
Staff_street
Not Null
Staff_city
Not Null
Staff_id
Staff_fname
Primary Key
Staff_lname
Status
Not Null
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
Consider the above entity relationship diagram, we find that there are entities with attributes and relationships with cardinality ratio defined in this diagram. Here cardinality is essentially the mapping of associated entities in the relationship.
www.programmingassignmentshelp.net
help@programmingassignmentshelp.net
Types of cardinality are: 1. 2. 3. 4.
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
Read more about Computer network Assignment help 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 (PRAKASH, prashant, 2012).
Task 5 Relational Data Model Tables with corresponding attributes and constraints – 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_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_city
Comp_id(PK),
Comp_fname,
Branch_lname,
Comp_lname,
Branch_phone,
Comp_phone,
Branch_street,
Comp_street,
Above database schema shows all the tables of Click Money database with their primary and foreign keys. Click Money Database screenshot using MS Access:
www.programmingassignmentshelp.net
help@programmingassignmentshelp.net
Above screenshot shows a table relationship diagram for Click Money Database in MS Access.
www.programmingassignmentshelp.net
help@programmingassignmentshelp.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. Read more about Computer Programming Assignment Help ===========================================================
Task 6 Please refer MS access database, which shows following things: 1. 2. 3.
The necessary constraints, and the appropriate relationships Database tables with sample records A functioning front end user interface that will suit client’s requirements.E.g. – Forms are created wherever necessary.
============================================================ ====================================
References:
AZHAR, muhammad. (n.d). What Is Data Dictionary In Dbms? [online]. [Accessed 1 july 2014]. Available from World Wide Web: <http://technology.blurtit.com/756573/what-is-datadictionary-in-dbms> BURNS, joe. 2014. To Use or Not to Use a Database? That is the Question. HTMLGOODIES, (n.d.) (n.d.), p.1. DATE, C. J. 2000. Introduction to database systems(7th edition). 2004. What are the difference between DDL, DML and DCL commands? [online]. [Accessed 1 july 2014]. Available from World Wide Web: <http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_com mands> PRAKASH, prashant. 2012. Basics of Normalization with examples. [online]. [Accessed 1 july 2014]. Available from World Wide Web: <http://www.aliencoders.com/content/basicsnormalization-examples> PURBA, sanjiv. 2000. High-Performance Web Database. CRC press. SILBERSCHATZ, Abraham, henry F. KORTH, and S. SUDARS. 2006. DATABASE SYSTEMS CONCEPTS. Mcgraw-Hill Higher Education.
Programming Assignment Help provides plagiarism free assignments i.e. all our solutions are genuine and written by best technical assignment writers who have years of experience. Get a quote now.
www.programmingassignmentshelp.net
help@programmingassignmentshelp.net