This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.Question: Draw an Entity Relationship Data Model which describes the content and structure of the data held by the research development
Structured Systems Analysis and Design Methodology (SSADM) is a formal, structured set of procedural, technical and documentation standards designed specifically for undertaking major software development projects. SSADM provides a lot of information about how that system works and becomes the basis of the requirements definition for the new system. Note that an existing system does not necessarily mean a computer system.
A very comprehensive methodology defines the analysis and design life cycles in terms of modules and stages. A variety of techniques are used including:
- Entity Life Histories using a Jackson-like notation
- Data modeling based on an Entity Relationship approach
- Data flow modeling
- Data normalization
SSADM is one of the most highly developed conventional analysis and design methodologies. It is conventional in the sense that it assumes a conventional analysis and design life cycle. It is a well-proven methodology that is freely available and openly documented.Entity Relationship Diagram (ERD)
Entity relationship diagram:
- is used to show the things or entities that are of importance in a system and how these entities relate to each other
- is used in the analysis of existing system and form part of the specification of the new system
- is used by the programmer and especially the Database Administrator
The relationship between two entities describes the way in which an occurrence of one entity is linked to, or influenced by, occurrences of another.
Set-up and test all of 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:
- Display the full name of all the employees (both internal and external) currently working on at least one project for the Research Department along with the project(s) they work on, and their roles in each project.
- Display all the projects that have received funding from a funding agency.the result should display the name of the project, the names of employees involved, the name of the funding agency, the funding contribution from the funding agency, the name of the project manager, the project total cost, and project end date.
To get the full name of all employees(both internal and external) currently working on at least one project for the Research Department along with the project(s) they work on and their roles in each project.
SELECT Project.ProjectID, Project.ProjectName, InternalEmployee.Name, InternalEmployee.Roles, ExternalEmployee.Name, ExternalEmployee.Roles
FROM InternalEmployee INNER JOIN ((Project INNER JOIN (ExternalEmployee INNER JOIN ExternalEmployeeDetail ON ExternalEmployee.ExternalEmployeeID = ExternalEmployeeDetail.ExternalEmployeeID) ON Project.ProjectID = ExternalEmployeeDetail.ProjectID) INNER JOIN InternalEmployeeDetail ON Project.ProjectID = InternalEmployeeDetail.ProjectID) ON InternalEmployee.InternalEmployeeID = InternalEmployeeDetail.InternalEmployeeID;
To get all the projects that have received funding from a funding agency the result should display the name of the project, the names of employees involved, the name of the funding agency, the funding contribution from the funding agency, the name of the project manager, the project total cost and project end date.
SELECT Project.ProjectID, InternalEmployee.Name, ExternalEmployee.Name, ProjectDetail.[Project Manager], FundAgency.FundAgencyName, FundAgency.[Contribution Amount], ProjectDetail.[Project End Date], ([Grant Amount]+[Outcome]+[Amount]+[Current Salary]) AS TotalCost
FROM FundAgency INNER JOIN (FundAgencyDetail INNER JOIN (InternalEmployee INNER JOIN ((ExternalEmployee INNER JOIN ((Project INNER JOIN ProjectDetail ON Project.ProjectID = ProjectDetail.ProjectID) INNER JOIN ExternalEmployeeDetail ON Project.ProjectID = ExternalEmployeeDetail.ProjectID) ON ExternalEmployee.ExternalEmployeeID = ExternalEmployeeDetail.ExternalEmployeeID) INNER JOIN InternalEmployeeDetail ON Project.ProjectID = InternalEmployeeDetail.ProjectID) ON InternalEmployee.InternalEmployeeID = InternalEmployeeDetail.InternalEmployeeID) ON FundAgencyDetail.FundAgencyID = Project.FundAgencyID) ON FundAgency.FundAgencyID = FundAgencyDetail.FundAgencyID;
Analysis and Assumption
for All task
Explain any assumption you have made when analyzing, designing and implementing the above database, justify the approach you have taken 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.Analyzing Stage
In task-1, we made analyzing the Research Development scenario to draw Database design and ERD (Entity Relationship Diagram).In thins stage, I emphasized which data, filed and information they want to know and analyze project process step by step. And then, draw ERD design.
In task-2, I analyzed ERD diagram to draw table design and to indicate clearly primary keys and foreign keys. And, I assigned data type for each table.
In task-3, I normalized resulting table and assigned primary foreign key and field size and table description easy to make in the database.
In task-4, I draw above normalized table in Microsoft Access database software to store data and record the project and employee data in database software.
In task-5, I set up and test all of the following queries using Microsoft Access (SQL) database. And provide print outs of SQL code and result table.Implementing Stage
- Selecting the DBMS
- Creating the physical schema
Establishing storage structures and associated access mechanisms
(Sequential File, Hashed File, Clustered File)
- Adding indexes
- Defining users and privileges
- Tuning in terms of the chosen DBMS
- Building integrity constraints
- Selecting and Exploiting my DBMS
All of the above stage, I have no very difficult case. I emphasize project process and which data they want to record and analyzed data structure on the scenario. All of the process, I made clearly such as form design, normalization, assign the data type and indicate primary and foreign key for resulting tables.Alternative Approaches and To Improve My Work
The database I've developed contains sensitive data to which we wish to limit access. I'd like to be able to create different classes of users so that some users have no access to this data, others can read the data but can't change it, and still others can modify the data. At the same time, I don't want to secure every object in the database this way; I'd like to apply security only to selected objects.
Access supports two forms of security: workgroup-based security and database-password security. If you use the simpler database-password security system, I can assign only a single password to the entire database, which is inadequate for our purpose. Before I can properly secure our database, I must have a security plan. Consider who will be using the database and what security permissions those users should have for each database object. With a plan in place, I can go about securing our database.Make a security plan
The first step in creating a security plan is to make a list of the people who will be using the database. Write out the names of the users and put them into distinct groups. A user can be a member of more than one group.
I use Microsoft Access in this system. Microsoft Access is chosen for relatively easy and still fair enough performance Relational Database Management System for medium size database. Tables are in indexed sequential file mode for better performance and faster searching. But Microsoft Access's disadvantage is that MS Access can append one time one row. By another approaches, I could use other commercial database management system sss(DBMS) products. They are Oracle, Progress, Sybase, Informix and IBM DB2. If I want to save the cost, I use open source database management system DBMS products. They are PostgreSQL, MySQL, MaxDB, Firebird, Ingres and Cloudscape.
- Access Cook book 2nd Edition (O'reilly)
- Access 2007 For Starters-The Missing Manual (O'reilly)
- Database Design & Development Assignment