Database design techniques

Database Design Techniques Database Assignment Help

PROGRAMMING ASSIGNMENTS HELP help@programmingassignmentshelp.net

Introduction Database management systems plays an important role in organizations as with the help of this system, an organization can maintain the details of inventory, customer, orders, empoyees, expenses etc. This stored data can be used in future as and when it is required i.e. Database Management Systems (DBMS) provide systems, tools and interfaces by which the organisation can manage their information and use it to assist in the effective decision making of the organisation. The collection of structure of the database is known as database schema. Schema specifies data, data relationships, and constraints on the data.

LO1: Understand Databases Design Techniques Assignment 1.1: Database development methodologies The process of developing a database can be divided into two main stages: 1.

2.

Data analysis, is done using formal methods to create a database design. Mostly used methods are Entity Relationship Modelling (ER) and Normalisation.

Physical implementation of that design in a database system. There are many databases tools/software available such as MS-Access, MySQL, Oracle, SQL Server etc.

The following table identifies each of the different disciplines and their equivalent terms in relation to the other disciplines.

Entity Modelling

Normalisation

DBMS

Entity

Relation

Table

Entity Occurrence

Tuple

Row or Record

Attributes

Domain

Column or field

Organization Information

Model Data

Data

Below are the details of each term defined in above table:

Entity Modelling:

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

Entity: A uniquely identifiable object of important from a top level perspective of an organisation or business model.

Entity Occurrence: A single instance of an entity.

Attribute: An identified element within an entity

Normalisation Terms Relation: A bottom up view of a design concept of a realisation of a potential database table taken from the movement of information within an organisation or business model Tuple: An ordered finite set of values of a relation.

Domain: Defines the constraint and type of a single value element of a relation.

Database Terms Table: A table is the conceptual view of the database’s internal structure in the context of the 3 layer model.

Record/Row: A row/record is a set of related data values of a common item.

Column: A column is a data value of a particular item type.

Database development lifecycle:

Main phases of database development lifecycle followed by SSADM are: 1.

2.

Feasibility study: This is process where business requirement is analyzed to determine whether a system can support the business requirements or not Requirement analysis: In this stage, the requirements of the system to be developed are identified, and the current business environment is modelled along with data structures.

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

3.

4.

5.

Requirement specifications: In this stage detailed functional and non-functional requirements are identified, new techniques are introduced to define the required processing/functionality and data structures that needs to be built.

Logical design: At this stage, different technical systems options are presented and the logical design on how data manipulation like update of data, insertion of data, deletion of data etc identified.

Physical design: At this stage, physical database design and program specifications are created inline with logical system specification and technical system specification created in previous stages.

SSADM (Structured system analysis and Design Method) is set of standards and principles for the system design. SSADM uses below mentioned three techniques to design a system:

 



Logical Data Modeling– This is the process where we identify, create model and and documents the data requirements of the system being designed. The data is categorized into entities and relationships (associations between the entities).

Data Flow Modeling– This is the process of identifying and documenting the data flow in information system. This process will examine processes (transformation of data from one form to another), data stores (which holds data), external entities (responsible for data feed to system and receiving data from system) and data flows (defines how data will flow in system).

Entity Behavior Modeling– This process will identify, model and documents the events that will affect each entity and the sequence in which it will occur.

As the above mentioned system models provides, different views for the same system but at the same time each view is important from system point of view which we are designing. These three techniques referenced against each other in order to ensure the completeness and accuracy of the whole system which is being designed.

As compared to Rapid application development where all steps are worked in parallel, SSADM will use the output of previous step as an input to next step i.e. sequential execution is being followed in SSADM.

1.2: ER Modeling and Normalization When a database is designed, an entity-relationship diagram is drawn at an early stage and developed as the requirements of the database. It helps in getting better understanding of business requirement/process. ER diagram creation helps to understand the data needs of an organization and it can be used as schema details for database.

Entity Relation Model of “Computer choice” case study and many to much relationship is resolved from the refined ER Model below to design database solution for Computer Choice upto NF3.

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

E-R Diagram for Computer Choice (first) The relationship describes the mapping of associated entity instances in the relationship. The values of connectivity are “one� or A one-to-one (1:1) relationship is when at most one instance of a entity A is associated with one instance of entity B.

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

For example, “employees in the company are each assigned their own office. For each employee there exists a unique office and for each office there exists a unique employee.

A one-to-one (1:1) relationship is when at most one instance of a entity A is associated with one instance of entity B. For example, “employees in the company are each assigned their own office. For each employee there exists a unique office and for each office there exists a unique employee.

A one-to-many (1:N) relationships is when for one instance of entity A, there are zero, one, or many instances of entity B, but for one instance of entity B, there is only one instance of entity A. An example of a 1:N relationships is A department has many employees, Each employee is assigned to one department A many-to-many (M:N) relationship, sometimes called non-specific, is when for one instance of entity A, there are zero, one, or many instances of entity B and for one instance of entity B there are zero, one, or many instances of entity A. An example is: employees can be assigned to no more than two projects at the same time; projects must have assigned at least three employees A single employee can be assigned to many projects; conversely, a single project can have assigned to it many employee. Here the cardinality for the relationship between employees and projects is two and the cardinality between project and employee is three.

Many-to-many relationships cannot be directly translated to relational tables but instead must be transformed into two or more one-to-many relationships using associative entities.

ComputerChoice relation details below in below table Relationship Type

Entity Class

Entity Class

Cardinality Ratio

buy

Customer

Sales

one-to-many

handle

Employee

Sales

one-to-many

contain

Sales

Product

one-to-many

comes

Product

Supplier

one-to-many

deals

Supplier

Manufacturer

one-to-many

ER Diagram is modified based on 1NF, 2NF and NF3 normalisation

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

 

Schema does not repeating Elements or Groups of Elements, all tables have single tuple and each cell of tables have a single value of attribute hence it is following atomicity, No multivalued attributes to fulfil 1NF. In Some table multiple making a primary key , No partial dependency and transitivity dependency so it follows 2NF and 3NF also

ER-Diagram after normalisation

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

ComputerChoice relation details below in below table Relationship Type

Entity Class

www.programmingassignmentshelp.net

Entity Class

Cardinality Ratio

help@programmingassignmentshelp.net

has

Customer

Address

one-to-one

has

Employee

Sal Address es

one-to-many

After Normalisation all attribute are atomic, partial and transitivity relation is removed to design database upto 3NF normalization.

Table Schema Table1: Customer (c_Id, c_Name, c_Email, contact,a_Id) Table 2: Employee (e_Id, e_Name, e_Email, contact,a_Id) Table 3: Address (a_Id street, city, state, zipcode) Table 4: Sales (InvoiceId, description, quantity, price) From ‘buy’ Relation: Table 5: CustomerBuy (saleId, c_Id, saleDate) From ‘handle’ Relation Table 6: EmployeeSale (saleId, e_Id, Date, Details) Table 7: Product (p_Id, s_Id, configuration, quantity, price, StockLevel) From ‘contain’ Relation Table 8: SoldHardware (saleId, p_Id) Table 9: Supplier (s_Id, s_name, s_phone, s_email, s_address, m_Id) Table 10: Manufacturer (m_Id, m_name, m_phone, m_address) All 10 tables follows normalisation mentioned below

  

1NF: contains, no multi-valued Attribute 2NF: no partial dependency 3NF: no transitive dependency

LO2: Database Desiging and implementation 2.1 Database Design for Computer Choice Design and implementation of SSADM for Computer Choice System is explained below with all the required diagrams, including all the stages of SSADM and how it convert requirement to a proper database design for Computer Choice System.

Phase 1: Feasibility Study

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

Feasibility Study is conducted for ComputerChoice requirement and Problem statement and found it is technically feasible. Dataflow diagrams are designed to shown how system data will flow between entities.

Below is the Level 0 Dataflow Diagram help us to understand System at higher level. The business area is analyzed to determine whether a system can cost effectively support the business requirements.

Base on data flow of the system Dataflow Diagram is created in detail as below

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

Module 1 the feasibility study consists of a single stage (Stage 0 Feasibility), which involves conducting a high level analysis of a business area to determine whether a system can cost effectively support the business requirements. In stage 0: an overview Level 0 DFD is produced together with a DFD. At this stage the DFD will represent the existing system and contain unresolved M:M relationships and Logical Structure Diagram also created as below

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

Phase 2: Requirements Analysis During stage 1 the systems requirements are identified and the current business environment is modelled in terms of the processes carried out and the data structures involved. During stage 1 DFDs and an LDS are used to produce detailed logical models of the current system. During stage 2 final business system option is adopted and refined. The transition from stage 1 to stage 2 is a key part of SSADM, this is where we move from a logical model of the current system to a logical model of the required system, and i.e. this is where the DFDs and LDS have to be refined to cater new/changed requirements.

Module 2 requirements analysis consists of 2 stages;





Stage 1 Investigation of Current Environment and Stage o Establish analysis framework o Investigate and define requirements o Investigate current processing o Investigate current data o Derive logical view of current services o Assemble investigate results Business System Options (BSO) o Define business system options o Select business system options o Define requirements

The requirements of the system to be developed are identified and the current business environment is modelled in terms of the processes carried out and the data structures involved.

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

Learn about Moscow Analysis Assignment Phase 3: Requirements Specification Module 3 Requirements Specification consists of a single stage (Stage 3 Definition of Requirements) which involves further developing the work carried out in module 2, detailed functional and nonfunctional requirements are identified 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. Detailed functional and non-functional requirements are identified and new techniques are introduced to define the required processing and data structures.

Phase 4: Logical System Specification Technical systems options are produced and the logical design of update and enquiry processing and system dialogues It includes two stages that are often made simultaneously.



The Technical Options of the System

A cost benefit analysis is made in order to choose an option. The beneficiary has also a decisive role in the choice of this option (or combination of options). This stage result is the technical selected and detailed option. The information will include: technical details referring to hardware and software, the system’s working way, the costs, the significant characteristics of the projection stage. Information about the possibility of improvement, maintenance and development of the system will also be presented.



The Logical Projection

A detailed logical pattern mainly containing the data projection and the procedures projection is elaborated. The data projection is made by using the normalization entities data base technique. The procedures projection is made by elaborating several detailed processes outlines. The data projection and the procedures projection is made simultaneously.

Phase 5: Logical Design It include detailed logical design and Final Entity relation diagram

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

ER Diagram Final (Same as Task 1.2)

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

Phase 6: Physical Design Module 5 Physical Design consists of a single stage (Stage 6 Physical Design) in which the logical system specification and technical system specification are used to create a physical database design and a set of program specifications.

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

ComputerChoice Database Design is being done using SSADM (Structured Systems Analysis and Design Method)

Read about E-Commerce Website Designing

2.2 Database screen shots Screen shots of Tables and forms are shown below, these are created using MS-Access for the database design using SSADM in task 2.1 Tables Customer

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

Employee

Address

Sales

CustomerBuy

EmployeeSale

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

Product

SoldHardware

Supplier

Forms Computer Choice Main Form

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

Class representations of database tables are as below

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

Also Read: Virtual Private Network

2.3 Methods of improvement Evaluating effectiveness of the database solution is done based on the requirement gether from case study, current design is fulfill all the requirement of System, though it has scope of improvement and future enhancement like we can include MS Access, reports , more event driven and database physical structure can be enhance to support high performance by using Hint, Indexing and tuning for the Computer Choice database solution and will help in future when data will be huge in database Enhancement to improve effectiveness of database system we can include the services as follows:

  



  

Security – user authentication and authorization improve Database effectiveness Building and Expanding Cleaning-up– The Cleaning-up Process is very effective and helpful for every organisation; it keeps the data updated and refreshed all the time. The Cleaning-up Process involves deletion of inaccurate or inactive records; merging of duplicate entries; creating E-mails using organisations E-mail formats; separating inoperative companies or candidates; updating database with the latest information; and more.

Daily updating –The Daily Updating Process involves the analysis of information provided by an organisation. In this process, the recruitment process outsourcing firm provides updated information to the organisation, about candidates, clients/customers, schedules client call, and arranges an interview for an applicant.

Backup and Rocovery: Backup and recovery is very important enhancement for system to be available maximum time Improve data integrity and concurrency Properly follow ACID property oft transaction.

Queries 1.

To get all customer and its address

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

SELECT

Customer.c_Name,

Customer.c_Email,

Customer.contact,

Address.street,

Address.city,

Address.state, Address.zipcode FROM Address INNER JOIN Customer ON Address.[a_Id] = Customer.[a_Id]; Result

2.

SELECT

Get all Product , Supplier and its manufacture

Product.configuration,

Supplier.s_Name,

Supplier.s_Phone,

Manufacturer.m_name,

Manufacturer.m_phone FROM (Manufacturer INNER JOIN Supplier ON Manufacturer.[m_Id] = Supplier.[m_Id]) INNER JOIN Product ON Supplier.[s_Id] = Product.[s_Id]; Result:

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

3.

To get all Employee how sold any Item

SELECT Employee.e_Name, Employee.e_Email, EmployeeSale.Date, EmployeeSale.Details FROM Employee INNER JOIN EmployeeSale ON Employee.[e_Id] = EmployeeSale.[e_Id]; Result

2.4 User Guide User Support Documentation ï‚·

Design of Database is done using MS Visio based on SSADM concepts

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net



Implementation is done using MS Access

Technical Documentation    

Database related information Please refer to 1 and 1.2 SSADM details in 1.1 DED, Logical, physical diagram, ERD, normalisation please refer to 1.2 and 2.1 Tables, forms – 2.2

Read more about Computer programming assignment help

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net

Conclusion This is to conclude that Key issues of data management systems and Relational Model Concept (domain, attribute, etc) and Relation Keys like (super key, candidate key, primary key, Relationship between identity keys and foreign keys is discussed. Application of Database for multiple Domain and advantages of Database is also discussed. SSADM database developmental methodology and its all phases are discussed in detail. SSADM (Structured system analysis and Design Method) is set of standards and principles for the system design. SSADM uses

  

Logical flow Modeling Data Flow Modeling Entity Behaviour Modeling

Entity-relationship modelling of Computer choice and as one-to-one, one-to-many and many-to-many relationship and normalization with 1st, 2nd and 3rd normalization to design database solution for Computer Choice ERD which is normalized to 3NF. SSADM implementation is shown with the entire supporting diagram, Database is designed and created in MS Access, screen shot of table, and form is attached. Other task includes future improvement, user guide.

Reference List        

Date, C.J., Introduction to Database Systems (7th Edition) Addison Wesley, 2000 Elamasri R . and Navathe, S., Fundamentals of Database Systems (3rd Edition), Pearsson Education, 2000. http://webcache.googleusercontent.com/search?q=cache:http://www.cdc.gov/cancer/npcr /tools/security/encryption2.htm Gartner ranks data quality management software, reveals trends – Hannah Smalltree http://searchdatamanagement.techtarget.com/news/article/0,289142,sid91_gci1263861,00.ht ml Data Warehousing Special Report: Data quality and the bottom line -Wayne W. Eckerson Komartin, R., Metodologii cadru de implementare a pachetelor software integrate, Ed. ASE, 2002 http://www.ehow.com/list_7485401_stages-ssadm.html

www.programmingassignmentshelp.net

help@programmingassignmentshelp.net