Draw an Entity Relationship Data Model
Draw an entity Relationship data model which describes the content and structure of the data held by the research department Data Modeling Using Entity-Relationship Model.
The Data modeling is an important phase in the database design process. and the Entity - Relationship Model has a top conceptual data model the model has easy display and analyze requirement, design the data and functions of the system and then implement the operations in the system.
Produce the resulting tables clearly indicating the primary and foreign keys.
The primary key is associated with the table in one of the key candidates, and there is only one.
Select primary key principles: must not be null (Not Null): candidate key property value can not be null, if the composite key, all properties are guaranteed not to empty value. will never change (Never Change): candidate key property values will never change. short and simple values (Brevity and Simplicity): select a single attribute as a candidate key.
Primary keys may consist of a single attribute or multiple attributes in combination.
Table usually contains one or more data rows in the table that uniquely identify each row value. This information line is called the primary key table (PK), mandatory integrity of the table with the entity. When you create or modify tables, the defined terms of PRIMARY KEY constraint to the establishment of the primary key. A table can have only one PRIMARY KEY constraint conditions, and any conditions added PRIMARY KEY constraint information line in unacceptable Null value. PRIMARY KEY constraint conditions as the only guarantee that the information, they are usually defined on the Identification field
Foreign key (FK) is used to establish and enforce the two tables of a data link between data lines or data lines combined. You can create or modify data in the table definition of FOREIGN KEY
Constraints to build an external index key. In the foreign key reference, when a data table storing key information on the main index of another table row by row reference data, the two will create links between tables. This information line will be the second table of the foreign key Foreign key is associated with a single attribute table or a set of attributes set, its value is the reference to other related table primary key, of course, may also refer to the table with an associated primary key.
Foreign keys and other related table is the corresponding primary key in relational databases is the link to play the glue function associated with the table. Foreign key must reference the primary key of the other related tables, this is the link between the two related tables.
Foreign key in associated table are not necessarily the primary key.
Foreign key and reference the primary key is the same domain, but the property name can be different.
Foreign key and reference the primary key in a single property if the primary key, foreign key is a single attribute, the primary key is a set of attributes, foreign keys is the same set of attributes.
Normalization is only one of many database design goals.
Normalized (decomposed) tables require additional processing, reducing system speed.
Normalization purity is often difficult to sustain in the modern database environment. The conflict between design efficiency, information requirements, and processing speed are often resolved through compromises that include denormalization.
Using a Data Base Management System (DBMS) of your choice, set-up all the above normalised tables,and populate them with well- designed test data (minimum 5 records per table). Provide printouts of all tables.
Input all Database to MS Access2007
DBMS database management system (Database Management System) DBMS is a programming system, to define, describe and manage the database and the links between applications.
DBMS is a core part of the database system, built on the basis of the operating system, common features include the establishment and maintenance of databases, database definitions, data access, database operations management, and database organization, storage, management and efficiency optimization.
DBMS provided by the database vendors, managers of the system through configuration commands issued directly to the database.
Because of its set of data structures exist Meta data, as long as understand the meaning of the parameters, any person may modify or increase the use of DBMS data structures.
Database management system (Database Management System) is a large database manipulation and management software, is used to create, use and maintenance of the database, called DBMS. It unified database management and control to ensure the safety and integrity of the database. DBMS users to access data in a database, the database administrator to maintain the database through the DBMS work. It offers a variety of functions, enable multiple applications and users in different ways at the same time or different times to create, modify, and ask the database.
For examples : Librarian in search of a book, first search through the directory to find words of that book and ISBN, and then stacks to find the type of book shelves and shelves in the order in accordance with the size of the ISBN to find, so soon can find the books I need. Database, the data in the same books as the library, but also allow people to easily find Caixing. If all the books are not in accordance with the rules of the heap in all the shelves at random, then the borrower is simply no way people find books they want. By the same token, if a lot of data at random stacked together, people can not find this data set can not be referred to as "database.
" Database management system is improved from the library's management came. People will more and more data into the computer, and compiled by a number of good computer programs to manage these materials, these procedures were later referred to as "database management system", they can help us to manage input to the computer large amounts of data, as the library administrator.
By function, database management systems can be divided into six parts:
- mode translation: data definition language (DDL). Use it to write the database schema to be translated into internal representation. The logical structure of the database, integrity constraints, and physical storage structure of data stored in the internal dictionary. Database of various data operations (such as search, modify, insert and delete, etc.) and database maintenance and management are based on database schema based.
- application of the compilers: to access the database statement includes an application, compiled with support in the DBMS can run the target program.
- Interactive query: to provide easy to use interactive query language, such as SQL. DBMS is responsible for implementation of the query command and query results displayed on the screen.
- data organization and access: data storage devices in the external physical organization and access methods.
- Operation and Management Services: provides services operation and management and operational logs, transaction monitoring and security operations data integrity check, the concurrency control and system recovery capabilities.
- Database maintenance: database administrator to provide software support, including data security control, integrity protection, database backup, database restructuring and performance monitoring and other maintenance tools. Based on the relational model database management system has been perfected, and has been widely used as a commercial software industries. It is in the household server based distributed multi-user environment application, the application of the database system further expansion. With the new data model and data management to achieve the advancement of technology, can be expected DBMS software will also update and improve the performance, applications will be further widened.
Database management system (DBMS) in order for the Information Society on the demand for data management technology, developed rapidly in the last decade a new study. Computer data management technology has gone through three stages: the freedom of the user document form management phase the data organized and affiliated in their respective application procedures. Stage of the operating system file management file system file structure is given a uniform and common way to access the user can document the data and information as preserved in the computer system in, and can easily query.
Database management phase in order to meet a lot of data centrally stored and made available to multiple users to share the requirements and procedures completely independent of the data to minimize data redundancy, database management systems have emerged. Well-known database management system include :
- MS SQL
Features it provides are as follows:
- data definition features.
- data management capabilities.
- to protect the database.
- database creation and maintenance.
- the database transfer
Normalized tables for Database Management system
Database normalization purposes has remove repetitive (Eliminating Redundancy): is the establishment of the association table does not repeat the information, because information is not only wasteful duplication of database storage space, and will produce data maintenance problems.
Removal of inconsistent dependencies (Eliminating Inconsistent Dependency): data dependency is between the attributes associated with the table has a relationship, if the association table has inconsistent data dependency, these properties will be in add, delete or update information, resulting anomalies.
Normalization if a bit too fully implements, may be related to the table cut too small, will result in query processing often be associated with multiple related tables, to get the result, its execution time will increase, making the system less efficiently.
Standardization and efficiency of the system are mutually conflicting, which compromise the anti-normalization, so that part of the merger of the association table to go back, or do not perform to the very high normalized in order to improve system efficiency. . Regularization process a total of five stages of the normalization model, normalization of each type of order to deal with different attributes in the data dependency between (Dependency) problems.
A relation is in first normal form (1NF) if it does not contain repeating groups of data items and it has an identifying key.
A relation is in second normal form (2NF) if it is in 1NF and has no attributes that require only part of the key to identify them uniquely.
A relation is in third normal form (3NF) if it is in 2NF and no non-key attribute depends on any other non-key attribute.
Set-up and test the following queries using Structured Query
Language (SQL). Provide printouts of SQL code for each query and the output produced when you run the query in the database you have developed:
a) Display the name of all the projects currently taking place, along with the names and roles of the academics involved and the start and finish date of the projects.
b) Display all the academics who have been involved in at least one consultancy project as Project Manager. The result should return their name, name(s) of projects where they played (or still play) the role of the Project Manager, the start and end dates of these projects, the current percentage of their full time spend on this project, the current percentage of their full time spent in consultancy on all projects.
Explain any assumptions you have made when analyzing, designing and implementing the above database, justify your taken approach and explain any alternative approaches you could have taken to any of the above Tasks. Discuss any changes you would make to improve your work.
Use Normalized table and input all project data to analyzing and design the project all items, that is very easy to control and management project staff and the costs.
The Database management system (DBMS) benefit :
- Independence: the information changes, the program do not change.
- Maintenance: maintenance easier and low cost.
- Security: Information security control by the DBMS is responsible.
- the standard of: each row has only one code, name, format.
- the development of: DB, DBMS, AP flexibility to mix any Add a.
- Independence maintenance of safety standards?
Developing a. The advantages of using the database system
- Simplicity save space, simplify data management.
- Retrieve data faster speed
- The latest information
- Can reduce duplication of information
- Because each contains the same data file waste of space
- To avoid data inconsistencies
- Because when allowed to repeat the information exists, update information is likely to result in inconsistent
- information can be shared
- sharing: a database of individual data for many different users can share, that is, each user can access the same information (and can be used in different purposes).
- Can be forced to follow the standards (data representation)
- Standardized data format on the exchange of information between systems with special help.
- To provide decision support (Decision Support)
- Through appropriate data model (Data Models) the complex relationship between the data shown
- Fast backup (Backup) and recovery (Recovery) database
- To provide a different horizon ( View)
(Referencing and Bibliographies)
HN computing http://www.sqa.org.uk/e-learning/SDM04CD/page_11.htm
Database normalization http://en.wikipedia.org/wiki/Database_normalization
Database management system (DBMS) http://en.wikipedia.org/wiki/Database_management_system
What is DBMS http://www.wisegeek.com/what-is-dbms.htm
Object database http://en.wikipedia.org/wiki/Object_database
Data normalization base http://databases.about.com/od/specificproducts/a/normalization.htm
The Database Normalization Process http://www.informit.com/articles/article.aspx?p=30646
What is BCNF http://en.wikipedia.org/wiki/Boyce-Codd_normal_form
Normalization to BCNF www.cs.nott.ac.uk/~bai/DBS/lecture12.pp