1 / 23

Student Justice DBMS

Student Justice DBMS. Benjamin Cormier Craig Overmars. Final ERD . Final ERD[1]. vsd. Step 1 – Preparing Transaction Requirements. First we had to determine the transaction requirements; these included: Data entry Data updating & deleting Data queries. Step 1.1 – Identify Entity Types.

lottie
Download Presentation

Student Justice DBMS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Student Justice DBMS Benjamin Cormier Craig Overmars

  2. Final ERD Final ERD[1].vsd

  3. Step 1 – Preparing Transaction Requirements First we had to determine the transaction requirements; these included: Data entry Data updating & deleting Data queries

  4. Step 1.1 – Identify Entity Types Next we identified each entity we would require in the database, as well as each entities occurrence.

  5. Step 1.2 – Identify Relationship Types The relationships and multiplicity of each entity were then deliberated.

  6. Step 1.3/1.4 – Attributes & Attribute Domains The attributes of each entity were determined, as well as the attribute domains of each.

  7. Step 1.5 – Primary & Candidate Keys Primary and candidate keys were determined for each entity in the database.

  8. Step 1.6 – Use of Enhanced Modeling We could not find a suitable location in our database to apply enhanced modeling. We had no entities that could be further broken down by classification.

  9. 1.7 – Entity Relationship Diagram ERD Diagram

  10. Step 2.1 – Remove Features Step 2.1 involved removing features that were not compatible with our relational model. This included removing the following: Many-to-many binary relationships Many-to-many recursive relationships Complex relationships Multi-valued attributes Changes also had to be made to incorporate: Student must be able to commit a particular crime more than once, we must incorporate an additional ‘offense’ entity into our model. Multiple witnesses and pieces of evidence being connected to a single incident, we must incorporate even more entities.

  11. Step 2.2 – Derive relations from Local Logical Model We incorporated the changes made in step 2.1, and now will derive the relations our database will use.

  12. Step 2.3 - Normalization Next we had to validate each relationship, using up to the third normal form.

  13. Step 2.4 – Validate Relations In this step we had to validate the relationships against our user transactions. From looking at the relations we created, and comparing those to the required user transactions in our conceptual model, it was clear that the database had the ability to complete all user transactions.

  14. Step 2.5 – Define Integrity Constraints For this step, constraints had to be determined incase the data is updated or deleted. Each entity was examined, and the best constraints were determined

  15. Step 2.6 - Review After reviewing the data model and completing the six logical steps, all potential users agree that the local logical data model is a true representation of the user view. The users agree that the local logical data model will prove to be useful in generating an effective student justice database that is capable of meeting all of their needs.

  16. Microsoft Access Form

  17. Microsoft Access Form

  18. Microsoft Access Queries/Reports

  19. Microsoft Access Relationship

  20. Sample SQL Coding Create table Incident(incidentNovarchar(4) NOT NULL, studentNovarchar(4) NOT NULL, policeNovarchar(4) NOT NULL, incidentDatedate NOT NULL, primary key (incidentNo), foreign key (studentNo) references Student, foreign key (policeNo) references StudentPolice); Insert into Student values('1001', 'Jeff', 'Winger', 'M', '1-Jan-1970', '0001', '1000', '1', 'jeffwinger@crime.com');

  21. Oracle SQL+ Tables

  22. Oracle SQL+ Queries All incidents that occurred in 2011 Show all student police that have ever responded to an incident

  23. Microsoft Access vs. Oracle Differences: Size & Scalability Number of Users (200 vs. tens of thousands) Windows vs. Many OS Creating/Updating Tables (Easy vs. SQL) Ran into problems with Oracle

More Related