Download Database System Analysis: Member Management Case Study and more Study Guides, Projects, Research Database Programming in PDF only on Docsity!
Kriti Khoteja (HND / Fourth Semester)
- Part Contents
- Introduction
- Database
- Advantages of Database
- Disadvantages of Database
- Database Management System
- Key Components of DBMS
- Advantages of DBMS:
- Disadvantages of DBMS:...............................................................................................................
- Database Models
- Hierarchical Data Model
- Characteristics of Hierarchical Data Model
- Advantages of Hierarchical Data Model
- Disadvantages of Hierarchical Data Model
- Network Model
- Characteristics of Network Model
- Advantages of Network Data Model
- Disadvantages of Network Data Model
- Relational Data Model
- Characteristics of Relational Data Model
- Advantages of the Relational Data Model
- Disadvantages of Relational Data Model
- Object Oriented Database Model:
- Characteristics of Object-Oriented Data Model Kriti Khoteja (HND / Fourth Semester)
- Advantages of the Object-Oriented Data Model
- Disadvantages of the Object-Oriented Data Model
- Relational Database Model and Normalization:
- Data normalization:
- First Normalization Form(1NF)
- Second Normalization Form(2NF)
- Third Normalization Form
- Why we use up to Third Normal Form?...........................................................................................
- Why we choose RDBMS?
- Types of RDBMS
- Vendor-Specific
- Open Source
- Justification for choosing MySQL of Open Source?
- Comparison between open source and vendor source
- Conclusion
- Part
- Introduction
- Database Design
- Methods of Database Designing.......................................................................................................
- ER Diagram..................................................................................................................................
- Schema Diagram
- Crow’s Feet
- Data flow diagram
- Class diagram
- Purpose of using class diagram Kriti Khoteja (HND / Fourth Semester)
- Activity Diagram..........................................................................................................................
- Analyzation of design of Relational Database for optimization of system performance
- Consistency
- Isolation
- Durability
- Design of Member Management System
- Description of Membership Management System Database
- Design of Membership Management System
- User Manual
- Tip for Registration
- Frequently Asked Questions
- Testing of the system
- Purpose of testing
- Categories of Testing........................................................................................................................
- Black Box Testing
- White Box Testing
- Verification and Validation
- Data Type Validation
- Proper Testing of System
- Choice of Tools
- Integrated Development Environment (IDE): Visual Studio
- Programming Language: C# using .Net Framework
- Relational Database Management System and Server: MS SQL Server
- Platform: Web Application
- Data Dictionary used in Membership Management System Kriti Khoteja (HND / Fourth Semester)
- Improvements that can be made on future for effectiveness of the database system
- Features of Membership Management System
- Limitation of the system
- Evaluation of effectiveness of designed system against client requirements
- Clients’ requirements for the Membership Management System
- Benefits and effectiveness of the system
- Measure of performance
- Security
- Speed
- User Experience
- Implementation of different features for database maintenance and security:
- Database security - Login Validation - User-based Role
- Part
- Introduction:
- System Monitoring and Optimizing Tools
- Error Log
- Extended Events
- SQL Server Profiler
- Database Security
- Login Validation
- User-based Role
- Effectiveness of Database Management Tools Kriti Khoteja (HND / Fourth Semester)
- Advantages and disadvantages of Database Management Tools
- Future Improvements and Enhancements
- Improvements that can be made on future for effectiveness of the database system
- Conclusion
Characteristics of Object-Oriented Data Model Kriti Khoteja (HND / Fourth Semester)
Article on different types of Database Management System Kriti Khoteja Introduction As per the scenario, we are asked to develop a fully functional web-based application named Member Management System to handle all the Database Management System is a software, which allows users to create, retrieve, update, delete and manage data in a database. Various DBMS software such as My SQL, Oracle, and SQL Server etc. are used for performing such operations on database. In this task, I have analyzed different types of management system. The different types of database models haven been compared and contrasted. The different relational database models have been explained and the process of normalization has been assessed to present how they help to provide reliable and efficient data structures. Similarly, I have critically evaluated different management systems available in relation to open source and vendor-specific platforms, justifying the criteria used in the evaluation. Database A database is a collection of information that is organized so that it can be easily accessed, managed and updated. Computer databases typically contain aggregations of data records or files, containing information about sales transactions or interactions with specific customers (Hughes, 2019). The data can be accessed, modified, managed, controlled and organized to perform various data-processing operations. The data is typically indexed across rows, columns, and tables that make workload processing and data querying efficient. Different types of databases include object-oriented, relational, distributed, hierarchical, network, and others.
Advantages of Database
Some of the major advantages of database are:
- Reduced data redundancy.
- Reduced updating errors and increased consistency.
- Greater data integrity and independence from application programs.
- Improved data access to users through the use of host and query languages.
- Improved data security.
- Reduced data entry, storage, and retrieval costs.
Kriti Khoteja (HND / Fourth Semester)
- Facilitated development of new applications program.
- Standard can be enforced: Standardized stored data format is particularly desirable as old data to interchange or migration (change) between the systems.
- The conflicting requirement can be handled. Disadvantages of Database
- It increases the opportunity for personal or groups outside the organization to gain access to information about the firm's operation.
- It increases the opportunity for a fully trained person within the organization to misuse the data resources intentionally.
- The data approach is costly due to higher H/W and S/W requirements.
- Database systems are complex (due to data independence), difficult, and time- consuming to design.
- It is not maintained for all organizations. It is only efficient for particularly large organizations.
- Damage to the database affects virtually all applications programs.
- Extensive conversion costs in moving form a file-based system to a database system.
- Initial training required for all programmers and users. In enterprise applications, databases involve mission-critical, security-sensitive, and compliance focused record items that have complicated logical relationships with other datasets and grow exponentially over time as the userbase increases. As a result, these organizations require technology solutions to maintain, secure, manage and process the data stored in databases. This is where the Database Management System comes into play. Database Management System A database-management system (DBMS) is a collection of interrelated data and a set of programs to access those data. This is a collection of related data with an implicit meaning and hence is a database. The collection of data, usually referred to as the database, contains information relevant to an enterprise. The primary goal of a DBMS is to provide a way to store and retrieve database information that is both convenient and efficient (Craig S. Mullins, 2017). By data, we mean known facts that can be recorded and that have implicit meaning. For example, consider any kinds of the names, telephone numbers, and addresses of the people that is
Kriti Khoteja (HND / Fourth Semester) assist users in designing, modifying, managing, and processing databases.
- Database Languages: These are components of the DBMS used to access, modify, store, and retrieve data items from databases; specify database schema; control user access; and perform other associated database management operations. Types of DBMS languages include Data Definition Language (DDL), Data Manipulation Language (DML), Database Access Language (DAL) and Data Control Language (DCL).
- Run Time Database Manager: A centralized management component of DBMS that handles functionality associated with runtime data, which is commonly used for context-based database access (admin, 2013). This component checks for user authorization to request the query; processes the approved queries; devises an optimal strategy for query execution; supports concurrency so that multiple users can simultaneously work on the same databases; and
Advantages of the Object-Oriented Data Model
into the databases.
- Database Manager: Unlike the runtime database manager that handles queries and data at runtime, the database manager performs DBMS functionality associated with the data within databases. The database manager allows a set of commands to perform different DBMS operations that include creating, deleting, backup, restoring, cloning, and other database maintenance tasks. The database manager may also be used to update the database with patches from vendors.
- Database Engine: This is the core software component within the DBMS solution that performs the core functions associated with data storage and retrieval. A database engine is also accessible via APIs that allow users or apps to create, read, write, and delete records in databases. Advantages of DBMS: Using a database management system for managing the data offers various kinds of advantages which are discussed below:
- As all the application program should
Disadvantages of the Object-Oriented Data Model
representation and storage, DBMS provides an abstract view of the data to insulate application code from such details.
- A DBMS utilizes a variety of sophisticated techniques to store and
Kriti Khoteja (HND / Fourth Semester) retrieve data efficiently. This feature is especially important if the data is stored on external storage devices.
- If data is always accessed through the DBMS, the DBMS can enforce integrity constraints on the data. Also, the DBMS can enforce access controls that govern what data is visible to different classes of users.
- A DBMS schedules concurrent accesses to the data in such a manner that users can think of the data as being accessed by only one user at a time. Further, the DBMS protects users from the effects of system failures.
- The use of Database management system largely reduces the time for the development of the application. The application also tends to be more robust. Disadvantages of DBMS: There are some of the disadvantages as well when we work with the DBMS software which are listed below:
- For small and simple applications for single users a database system is often not advisable.
- A database system creates additional complexity and requirements. The supply and operation of a database management system with several users and databases is quite costly and demanding.
- The professional operation of a database system requires appropriately trained staff. Without a qualified database administrator nothing will work for long.
- Through the use of a database system new costs are generated for the system itself but also for additional hardware and the more complex handling of the system.
- A database system is a multi-use software which is often less efficient than specialized software which is produced and optimized exactly for one problem. Database Models A Database model defines the logical design and structure of a database and defines how data will be stored, accessed and updated in a database management system (What is a Database Model, 2019). Basically, it is a collection of conceptual tools for describing data, data relationships, data semantics, and consistency constraints. Some of the Data Models are:
Relational Data Model
This database model organizes data into a
Kriti Khoteja (HND / Fourth Semester) database model before Relational Model was introduced. Figure 2 : Network Model
Characteristics of Relational Data Model
- This model solves the problem of data redundancy by representing relationships in terms of sets rather than hierarchy.
- Computer programmers rather than users used implementations of the
Network Model
- Relationships are pre-defined.
- Navigation is done by the programmer.
Disadvantages of Relational Data Model
- This model is simple and the implementation is effortless.
- The network model has the ability to manage one-to-one (1:1) as well as many-to-many (N: N) relationships.
- Accessing the data is simpler when compared to the hierarchical model.
- In a network model, there’s always a connection between the parent and the child segments because it depends on the parent-child relationship. Disadvantages of Network Data Model
- Every record has to be maintained with the help of pointers, which makes the database structure more complex.
- Because a great number of pointers are essential, insertion, updates, and deletion become more complex.
- A change in structure demands a change in the application as well, which leads to lack of structural independence. Relational Data Model In this model, data is organized in two- dimensional tables and the relationship is maintained by storing a common field. This model was introduced by E.F Codd in 1970, and since then it has been the most widely used database model, in fact, we can say the only database model used around the world. The basic structure of data in the relational model is tables. All the information related to a particular type is stored in rows of that table.
Kriti Khoteja (HND / Fourth Semester) Hence, tables are also known as relations in the relational model. The relational database is the type of database in which the data are organized in the form of tables also known as relations. Though there are other kinds of databases such as Hierarchical, Network, and Object-Oriented, this is the most common database type in the market. The system of database management in which the data are kept in a tabular form so that data can be easily retrieved is Relational Database Management System (RDBMS). RDBMS basically contains data in row and column. Many tables are formed and each table consists of different records that can be recognized by the unique value which is defined in records and table. Some examples of relational database management systems include MySQL, Oracle, and Microsoft SQL Server database. Figure 3 : Relational Model Characteristics of Relational Data Model
- Built in data integrity.
- Data consistency and accuracy.
- Easy data retrieval and data sharing.
- Non-navigational in nature.
- Controlling redundancy Advantages of the Relational Data Model
- Structured independence is promoted.
- Users do not have to know the physical representation of the database.
- Use of SQL language to access data.
- Easier database design.
- The tabular view improves simplicity. Disadvantages of Relational Data Model
- People need training if they want to use the system effectively and efficiently.
- Substantial hardware and system software overhead.
- Possibility of poor design and implementation.
- Potential “islands of information” problems.
Object Oriented Database Model:
Object oriented data model is one of the developed data models and this can hold the audio, video and graphic files. These consist of data piece and the methods which are the
Kriti Khoteja (HND / Fourth Semester) below: i. Definition: A hierarchical model is a structure of data organized in a tree-like model using parent-child relationships while network model is a database model that allows multiple records to be linked to the same owner file. A relational model, on the other hand, is a database model to manage data as tuples grouped into relations. In object-oriented model, data is organized in the form of objects which are instances of classes. ii. Basis: Hierarchical model arranges data in a tree similar structure while network model organizes data in a graph structure. In contrast, relational model arranges data in tables. The object- oriented data in objects. iii. Relationship: Moreover, an important difference between hierarchical network and relational database model is that while a hierarchical model represents one to many relationships, a network model represents many to many relationships. Furthermore, relational model can represent both one to many and many to many relationships. The object- oriented model has direct relationship with data. iv. Accessing data: Although it is difficult to access data in the hierarchical model, it is easier to access data in the network model, relational model and object-oriented model. v. Flexibility: Another difference in database models is their flexibility. The hierarchical model is less flexible, but the network model and relational model are flexible. Object oriented model is very flexible than other data models and also has the feature of reusability. vi. Usability: Hierarchical model is a traditional model and is rarely used. The network model is used more than hierarchical model. The most widely used model is relational database model sue to its simplicity. Although, object-oriented database model has wide features, it is used relatively lesser than relational model due to its complexity and security.
Kriti Khoteja (HND / Fourth Semester) Relational Database Model and Normalization: A relational database (RDB) is a collective set of multiple data sets organized by tables, records and columns. RDBs establish a well- defined relationship between database tables. Tables communicate and share information, which facilitates data searchability, organization and reporting. (techopedia, 2016) Relational model is the most popular model and the most extensively used model. In this model the data can be stored in the tables and this storing is called as relation, the relations can be normalized and the normalized relation values are called atomic values. Each row in a relation contains unique value and it is called as tuple, each column contains value from same domain and it is called as attribute. In relational model, data is organized in rows and column structure which is two dimensional tables and the relationship is maintained by storing a common field. The advantages of relational database model are listed below:
- It is easier to use and maintenance of security is easier compared to other database models.
- Changes in the database structure do not affect the data access in this model. - The database design, maintenance, administration and usage are much easier than in this model than other models as the model supports both data independence and structure independence concept. - Using of complex query to accesses or modify the data from database is possible in this model. - The information can be revised easily tables consisting of rows and columns is much easier to understand. Data normalization: Data normalization can be defined as a process to develop the conceptual scheme from the external schema. Likewise, it is the process of organizing the attributes and relation of relational database to minimize data redundancy (What is Data Normalization and Why Is It Important?, 2019). It is a multi-step process that puts data into tabular form by removing duplicated data from the relation tables. Normalization is the systematic method of decomposing tables to eliminate fact redundancy and unwanted characteristics like insertion, update and deletion anomalies. It works to remove following anomalies: 1. Data redundancy: It is the useless data or data present in multiple lines of same columns.
Kriti Khoteja (HND / Fourth Semester) Table after 2NF normalization Student Age Adam 15 Alex 16 Stuart 20 Again, we create here another table for subject to minimize it in 2NF Student Subject Adam Biology Adam Maths Alex Maths Stuart Maths Hence, the data redundancy and partial dependencies are removed using second normalization form and data is more organized.
Third Normalization Form
3 NF states that it should meet all the rules of 2NF and there should not be transitive functional dependency. In 3NF following activities are done:
- It should be in second normal form.
- All non-key attributes should not be depended on any other non-key attributes.
- Each field should have a unique name
- It should have primary key Let normalize this table Student Age City Country Adam 15 Madrid Spain Alex 16 London England Stuart 20 Milan Italy Student Age City_id Adam 15 1 Alex 16 2 Stuart 20 3 City_id City Country 1 Madrid Spain 2 London England 3 Milan Italy
- Fig: table after 3NF normalization Hence, by using the three normalized form, the data are arranged in systematic way eliminating the data redundancy, anomalies and dependencies and makes easier understanding of the data. Why we use up to Third Normal Form? We have used up to Three Normal Form
Kriti Khoteja (HND / Fourth Semester) because of the following reasons:
- In First normal form we can remove duplicates and creates separates tables for groups of related data
- Whereas, Second Normal Form, we can remove subgroups of data present in multiple rows of a table and creates new tables, with connections between them via unique identifier
- Furthermore, in Third Normal form, we can expand or separate dependent columns into the new table. To clear the above justification, we can look over the example as I mentioned above in Normalization Figure. So, above are the main reason to use normalization up to 3NF. Why we choose RDBMS? RDBMS avoided the navigation model as in old DBMS and introduced the Relational model. The relational model has a relationship between tables using primary keys, foreign keys, and indexes. Thus, the fetching and storing of data become faster than the old Navigational model. So, RDBMS is widely used by enterprises and developers for storing complex and large amounts of data. The main reason RDBMS is so popular nowadays and the reason we use RDBMS in our project is that carrying out operations in RDBMS is very easy. Here in our Membership Management System, we need to insert, update and delete data several times. For instance, a member has to pay membership fee according to his/her designation and now you have to add this to your database. How do you do this? In the flat-file system, it will be very difficult to change table data. In this case, RDBMS makes it easy with its features like insert, update, join, etc. for the operation of data. Now if a member status has to be changed, we can use a simple update query to update the same member’s data that was already on our database. This helps to reduce data redundancy and makes the process faster. Types of RDBMS Well, Relational Database Management System can be classified in many terms. Here I have classified RDBMS on the basis of its reachability and adaptability. Hence there are two types of RDBMS that can be used in our Assignment Management System project.
Vendor-Specific
Vendor-Specific is a software, which is available in market at a certain price. Which means, whosoever needs to use it has to pay for it to get a valid and authorized license; of
