This Assignment is about: Database Management System Assignment Help
This document will provide you a thorough knowledge of database architecture, its designing and implementation. You will get acquainted with database concepts and will learn MySQL to implement the given database system. It will improve your programming and querying skills.
The designing of any database depends on its architecture. The architecture can be centralized, decentralized or hierarchical. Database architecture defines the architecture and internal processing of data. Each type and component of architecture has some basic functionality which is as significant as any other component in the system. There are basically three types of database architectures in database management system. In n-tier architecture, on the basis of value of ‘n’ the layers or levels are divided.
- 1-tier Architecture – In one tier architecture there are no intermediates as DBMS is the only entity user directly interacts with DBMS. No requirements for any database tool and preferably this type of architecture is used by database designers and programmers.
- 2-tier Architecture- In 2-tier architecture, an application layer is added through which user will access database management system. In terms of operations, functionalities and designing, the application is totally different from database.
- 3-tier Architecture– It is the most popular and widely used architecture and most database companies follow this architecture. In this each tier is separated with the other. The division is on the basis of complexity of the users. In three -tier we have three layers the presentation layer, application layer and database layer.
- Presentation layer: This layer is also called as user layer as user directly interacts with this layer and has no details beyond that. Several views of database are available at this layer.
- Application Layer: Here application layer is a mediator between the user and the database. User interacts with the application which in turns interacts with the database. It depicts the abstract view of database. The application server and all the programs reside in this layer.
- Database Layer: Here the actual database resides with query languages. DML and DDL languages are exits here. The constraints associated with each entity are also stated in this layer.
Data consistency is an important feature of database management system. To have consistent data analysis and you have win the trophy already. Well, from each activity or transaction data must be in consistent state. It may be inconsistent in between but once done at a particular state while traversing to other state it should be serialized and consistent. Every relation in a database will be in consistent state to ensure data consistency. As the information or transactions in context with database travels from one network to another it is mandatory to keep the data consistent for successful and commit transactions. Consistency is one of the ACID properties of database which says either the transaction must be done to its entirety or none. It should be in between stage of these two. There are two types of consistency we have encountered. They are Strong entity and weak entity. A diverse and distributed type of consistency is point in time consistency, transaction consistency and application consistency. Each one of them specifies whether data will be persistent even after the data damage, natural disaster, system crash, while installation or by any other means that is why it is very important to make data consistent.
The access methods to access a database are many and one of them is by firing queries. Structured query language is used to access data from the tables and the relations. SQL is a kind of English type language and is easily understandable by the programmer or designer. There might be the times
Methods of Data Access: Data Access refers to the all operations which stores, retrieve and update all the data in database.
There are two methods to access data:
- Sequential Access
- Random Access
Sequential Access: Sequential access refers to the access of a group of data in a predefined or ordered sequence. Sequential access is only method to access data in order.
Random Access: Random access refers to the access of a specific data based on desired coordinates from the bulk store of data in database. Random access will access the data as easily, safely and quickly as another data from database.
Methods of Data Recovery: Data Recovery refers to the recovery of data from damaged or corrupted secondary storage such as removable disk, media files etc. the data is most often recovered from the secondary devices such as hard disk drives, solid-state drives, USB flash drives, magnetic tapes, CDs, DVDs, RAID etc.
- Salvation program: This program is run after crash or damage of data to restore the data or get the data back.
- Incremental dumping: This method is used to update the data which are stored in archive for further backup.
- Audit trail: This refers to the sequence of operations which are applied on the stored files for the backup.
- Differential files: This process keeps all the changes which are made and then merge these all changes with the original file.
- Backup/current version: This process keeps all the changes which are made in current database.
- Multiple copies: This process keeps multiple copies of data so that these can be used further in case of damage and corrupt.
- Careful replacement: This process keeps the original file and made the required changes but it doesn’t delete any data after confirmation of changes it delete unused data.
Methods of data security: Data security refers to the security of data from unwanted or unauthorized access. It also maintains the confidentiality, integrity and availability of data in database.
- Encryption: This is a process of transforming information from plan text to another text using some encryption methods and makes them unreadable for all except experts who are aware of the method of encryption.
- Web Based Data Security: This security is provided while transfer of data from client to server or from server to client. Using this method this is determined that the transfer of data is occurring in safe manner without any interruption.
- Negative Database: In this method false data is added to original data to make the data useless.
- Authentication and access control: only the authenticated person who has the right to access the data can only read or access the data. The unauthorized user is not allowed to access any data.
- Timeliness and security in real time database system: Different methods are used while transaction of data in real time. These all methods maintain the security of data.
- Testing scheme for SQL injections: This testing scheme tests all the operations performed on the SQL database to maintain security.
Role of Database Administrator is to maintain database integrity and a database administrator is responsible to maintain the data of all databases within organization.
Roles of database administrator are:
- Implementation of database models: The main responsibility of a database administrator is to implement all database models within organization.
- Database Design: The database administrator is responsible for the design of database. This is the role of administrator is to maintain the integrity and consistency of database.
- Database Accessibility: The database administrator is responsible to maintain all the access of data. The access of data must be secure and in appropriate manner so it doesn’t harm another data.
- Performance Issue : The database administrator is responsible for the performance of any database such as it should not harm another data while access, it must be safe, it should not be corrupted and also must be safe unauthorized access.
- Capacity Issue: the database administrator is responsible for any capacity issue of database. The administrator must define that the database can hold what kind of data and how much data.
- Data Replication: The database administrator is responsible to maintain the consistency of database so no data is repeated and must be consistent.
- Table Maintenance: This refers to the maintaining of table so that the accurate or useful information can be stored in database the unused data must not be there in tables.
Role of Industry Standard: Industry Standard refers to the rules and regulations under which an industry works or function and carry out all the operations which are needed to perform the industry work.
- Administration and the legislative bodies are also benefited by the Industry standard. They govern the practical as well as the technological standards as per the legal requisites.
- Standardization facilitates a healthy competition and designing of new concepts.
- Industry standard ascertains the rank of an industry in the economic set up of a country.
- Optimum standards facilitate the creation of political as well as business related advantages. The reason being that the industry standard is worked out in consonance with the expertise of the corporate houses and different segments of the society.
Database development methodology is part of the system development life cycle and it used to develop database accurately from the requirement analysis. According to the requirement of HPI structured system analysis and design methodology will be implemented for this. It is Iterative approach for Database development as the firm will keep vehicle information in their database which requires broad user involvement. The database for HPI will be reusable from the context of updation of details with the cases. Basically the iterative approach offers the great opportunity for successful implementation of the database. This approach is selected as it is simple to understand, simple to use and will be deploy in shortest time. The database will be developed for the HPI with following phases:
- Feasibility Study
- Requirements Analysis
- Requirements Specification
- Physical Design
Feasibility study: feasibility study is conducted for HPI to store vehicles information. This is start with the definition of problem that is shared access of the database. The proposed system is completely feasible for the HPI database application. The feasibility study also includes the alternative option for the system.
Requirements Analysis: The requirement of the HPI is fulfilled after the investigation of the current system. Business system requirements are the main motive of this phase. The requirements of the HPI are basic but the result of this phase includes some more requirements for the proposed system. For example the car details will be provided to insurers but this process can be automated if the insurers’ database is dynamically verified by the HPI proposed database. The result of this phase will be foundation for implementation of the database development. Basically the current methods is analysed to grab the latest requirements for the system. The requirements will be help in logical model. This analysis also results in the functional and non-functional requirements of the system. Functional requirements are those by which user access the database system and non-functional requirements are those by which user related indirectly i.e. database response time etc. The deliverable product foundation will be developed in this phase.
Requirements Specification: In this phase SMART (Specific, Measurable, Achievable, Realistic and Timely) Results will be achieved for the proposed database system for the HPI. With this phase specific requirements are shortlisted that is what data to be stored in which table and what entities will contained by the which table are observed, for example vehicles details will be stored in three table, one table will contains the details of all vehicles, second contains the details of stolen vehicle and third will contain fraud claimed vehicles. It is the main phase of the HPI database development model as it results the final specification and after it we can further to database design and development. The specific requirements helps in the measurable amount of database size required for the system. The achievable requirements only be part of in final requirement catalogue. On the basis of the specified requirements database estimates are shown to the HPI firm.
Physical design: The logical design of the database system will be presented to the HPI firm, to start implementation. The logical design of database is the output of the SMART phase. physical design of the database includes the mapping of the functional component. This phase has the dependency on the client for approval of the database design. it is clear statement of the database that includes storage amount, data structure, table relationships, technology to be used etc. the database architecture role will be start after this phase.
The entity-relationship model is the best data modelling technique with this diagram it is easy to design the database that results easy development procedure at the time of implementation. The entity relationship diagrams displays relationship with entity, attributes and their relative notions. The entities are independent and object of real world. Attributes defines each entity. For HPI database system the entities will reflect the following information system:
- Firm: This entity is developed to store the different firm. It has 4 attributes.
- Insurer: This entity holds the details of insurer that insure the vehicle. It has 6 attributes that are respectively id, name, registration number, vehicle name, number and status.
- Stolen vehicle: This entity holds the claims of stolen vehicles. It has 6 attributes i.e. vehicle colour, vehicle name, insurer id, purchase date and stolen date.
- Fraud vehicle: This entity captures the vehicles with fraud claims. It has 5 attributes i.e. vehicle colour, vehicle name, vehicle id, purchase date, case date.
- Driver: This entity holds the number of person drive the specific vehicle. It will be helpful for stolen and fraudulent case. It has 4 attributes id, name, criminal case, disqualified date.
Read more about Computer Programming Assignment Help
Normalisation is used reduce redundancy and increase data consistency. Normalization is the process of organising the data in the database with complete efficiency. For the database system of HPI normalization is a must process that is to be followed as lots of data will be inserted, retrieve and update. It will help in reduce the redundancy and increase the data consistency. This is important for HPI database system as it will manage the consumed database size and also validation is implementing as data will be stored in logical way.
LO 3 Be able to design, create and document databases
HPI firm’s requirement will be fulfilled by building relational database system using Ms-SQL. The database development cycle is part of software development lifecycle and individually works in same form as software development lifecycle. For the HPI firm database is developed using iterative approach and SSADM (Structured Systems Analysis and Design Methodology). This methodology supports iteration and fulfilled the requirements in appropriate manner. The database is implemented in following six phases.
- Database study
- Database design
- Implementation and loading
- Testing and evaluation
- Maintenance and evolution
Here we discuss the phases of database development cycle:
- Database study: This phase includes the study of the database existing and proposed requirements. With the SSAD methodology, requirements specifications are meeting. HPI firm, wants to upgrade their operations, it will helps in reducing fraudulent claims to the insurers. So it is clear that there main requirement is make a database that only allows one vehicle only be insured by only one insurer. It will help them for genuine claims proceeding. The database will relational as it will be connected to the insurer’s database.
- Design: The SMART process results the logical design of the database. On the basis of logical design the real database is developed. Throughout the design process pieces conjoint in RDBMS. For the HPI database Ms-SQL is used as server. The relational database management system is design will fulfil the all requirement of HPI firm.
- Loading and development: In this phase, the tables will be load and database will be developed. This phase will also perform the testing of the developing database. In this phase logical design and technical specification management is tested. The database configuration and field constraints are set in this phase.
- Evaluation and Testing: The implemented database is assessed and tested in this phase. The database is tested by analysing the mapping of the database. The access methods and key management in the database is managed for the HPI firm. This phase narrates about the performance analysis of the developed system.
- Operation: in this phase, operations are performed on database. All the functionality is applied for the developed system. For HPI firm data with different tables has been analysed.
- Maintenance and evolution: with this phase maintenance plan has been developed for the database. it is known thing that end user will involve in the application of database but if any malware attack or any other vulnerability occurs on the database, what to do and how rescue operation is perform, is decided.
3.2 design a fully functional database (containing at least four inter-relational tables) including user interface
This developed database is RDBMS and it is based on the given requirements. With this following things can be performed:
- Entering and maintaining vehicle, insurer and firm information
- View information from all the tables
- Insertion and maintains stolen and fraudulent vehicle details
- View reports
3.3 evaluate the effectiveness of the database solution and suggest methods of improvement
The developed RDBMS of HPI firm will manage the whole fraud detecting activity and manage the things that are effective administration, fraudulent vigilance and information management. This database system will be responsible to serve Association of British Insurers (ABI) and positive results from its implement will cut insurance costs for honest motorists. HPI is firm that keeps the information of all vehicles and it only allows that one vehicle will only be insured by once. The developed functional database will handle the vehicle details, stolen vehicle, and insurer details. The developed database is restricted and required to be more restricted as it contains the valuable information. The developed database can be more effective with following methodology
Database Expansion: As the data will be continuous incremented, thus database must be expand with the time. This database can be expanding more by building process. With that more information can be stored according to the requirement
Expanding and Building- In the Expanding and Building Process, ABC ltd. required to verify the requirement of existing data. Building of existing data helps the company so it can easily access the data as per their requirements. Furthermore, in this database we can implement more information according to the requirements.
Cleaning– In the such kind of database where the information insertion will be done in continuous way, cleaning of database plays important role. For the cleaning of HPI firm will erase the replicate and old data automatically. It is need of time as new data will requires space and if there is option to make space then choosing it is good idea. The cleaning process includes the removal of inactive tables, duplicate entries and propagates table merging.
Regular Update – Regular updating is good practice but for the HPU firm it will be updated daily but there are table which holds the ‘pending statements’ such the cases where single vehicle have multiple insurance so that will be flagged as fraudulent status. So if the database reminds the admin about such pending tables, then it will be greater as one table will be removed from one flag to another flag. The daily updation helps to work on such cases easy.
Read more about Database Assignment Help
In a technical documentation the functionality, architecture and handling methods are provided. The documentation will work for the end user and administrator. The documentation will define external and internal dependencies of the system.
In the developed database system, the database of firm and insurer is directly connected and they are relational table. The details of vehicles are saved separately where the details of stolen vehicles and fraudsters will be managed separately. The number of driver that drive specific vehicle will be saved in separate table.
With this database HPI firm will be able to store the details of all vehicles, stolen vehicles and fraudster vehicle. Each vehicle has contains the foreign key with the status i.e. stolen and fraudster. This will help in identifying the status of the particular vehicle.
The technical documentation contains the following thing described in this reports earlier section.
- Architecture or Design of system – the design of the system shows the interface and how the data will be store in the backend.
- End user- It discusses the way by which end user of the system how interact with the system. Since this application is implemented in backend only and front end yet to construct, is not provided.
- Technical – It consists of documentation of used code, set of tables, interfaces, and APIs.
- Requirements– To run this database a system required to have Ms-SQL.
The developed database will fulfil the requirements of HPI firm, with this application firm will manage the data in efficient manner. With this methodology of database development is understand in effective manner. The designed database is based on the actual requirements of HPI firm and serves according to that. The number of requirements has enhanced after implementing research methodology.
- Lutron, N. (2015). Technical Documentation & Support Overview.Lutron.com. Available at: http://www.lutron.com/en-US/Service-Support/Pages/Technical/Overview.aspx [Accessed 09 Dec. 2015].
- Radu, D. (2013). Marketing Database Development Methodology – Macromator. [online] Macromator. Available at: http://macromator.com/marketing-database-development-methodology/ [Accessed 9 Dec. 2015].
- tutorialspoint.com, (2015). DBMS Architecture. [online] Available at: http://www.tutorialspoint.com/dbms/dbms_architecture.htm [Accessed 10 Dec. 2015].
- tutorialspoint.com, (2015). DBMS Architecture. [online] Available at: http://www.tutorialspoint.com/dbms/dbms_architecture.htm [Accessed 10 Dec. 2015].
- microsoft.com, (2015). Data Administrator role. [online] Available at: https://technet.microsoft.com/en-us/library/dd252702(v=office.12).aspx [Accessed 10 Dec. 2015].