This is a solution of Time Tracking Database System Assignment Help in which we discuss Developing Entities have identification with the attributes.
Entity relationship model
Entity relationship diagram represents the data entities and their association with other data entities in database so that diagram of inter relationship can be used to perform business activities. It gives concept of data arrangement and relations in database development. Al though, ER diagram does not define any operation to perform in business prospective but gives basic idea of data model in which data is arranged to meet business operations. Entities have identification with the attributes.
Components of ER Model
Entity: Smallest and atomic values in database relations are entity which is real world objects. Entities are represented with the help of rectangle diagram. School, mobile, watch etc are example of entities (Teorey.et.al.2011).
Attribute: Entities are identified with their properties which is called attribute. Oval shape in ER diagram represents attributes. Attribute may be of following types:
- Key attributes: Attribute in database those are capable to identify the entire record form table are key attributes. For example, roll number and mobile number may be key attribute to identify record from tables.
- Composite attributes: attributes those have composite values in columns are called composite attributes. For example name of student is composite in nature and can be transformed to atomic attributes as first name, middle name and last name.
- Derived attribute: Attributes those are derived from other attributes are called derived attributes. For example, age can be derived from date of birth column.
Relationship: how to entities are interdependent to other entities in other tables of database are determined with help of relationship diagram. Relationship may exist in three forms:
- One to one relationship (1:1): In it, one entity of table has relation with other one entity in another table. For example, one monitor in one class is one to one relationship (De Simone.el.al.2010).
- One-to-many relationship (1: M): Relationship of one entity with more than one entities in another table is one-to-many relationship. For example, one class can contain more than one student.
- Many-to-many relationship: Relationship of more than one entity from a table to other tables is called many-to-many relationship. For example, many students can participate in many subjects (Ambler, 2012).
Normalization
Process to eliminate data redundancy and anomalies of update, insertion and deletion in database is term as normalization. Normalization process is effective to save space and to arrange data in more manageable form. Primary table may be split into sub tables during normalization process. Three basic normalization forms are:
First Normal Form (1NF): As per the statement of first normalization form, every column must need to contain only atomic values (Gupta.et.al.2010). Not a single field of table can contain group of information or values.
Second normalization form: For a table to be in second normal form a table must need to be first normal form and there should be a column, primary key on which all other attributes are dependent. This is required to indentify a record uniquely during the update and deletion process to remove anomalies. This normalization form states that there is no independent attribute in table.
Third normalization form: For a table to be in third normalization form, following condition must meet:
- Table must need to be in second normalization form as it also will contain the characteristic of first normal form.
- There should be no transitive dependencies among non-prime attributes or there should be no relationship among two non-prime attributes.
Let an attribute X which is related to Y and Y is associated to Z then x -> Y ->Z means X ->Z which is transitive in nature. Prime attributes are those which have capability to determine the record form table as they are identifier for records in table. Third normalization form with extensive elimination of transitive dependency and determination of non-prime attributes from non-prime attributes brings the following advantages in database:
- Data integrity is achieved in database as normalization leaves the unique entries those are easy to update and insert.
- Data duplication is avoided after the normalization and it saves space in storage medium. It is also remove anomalies during operations on database (et.al.2011).
Crime-Tracking Database System
Following are the tables which will be used for crime tracking database
Table 1: Criminals
The table would store the personal details of the criminals
| Column Name | Data type |
| criminal_id | Number |
| criminal _name | Varchar2 (40) |
| criminal _add | Varchar2 (240) |
| criminal_status | char(1) |
| criminal_probation | char(1) |
Table 2: Criminal_phone_details
The table would be storing telephone number of criminals and the relationship between criminal and criminal_phone_details would be one to many.
| Column Name | Data type |
| criminal_id | Number (8) |
| criminal_phone_no | Number (12 |
Table 3: Alias
The table would be storing alias names of criminals and the relationship between criminal and alias would be one to many.
| Column Name | Data type |
| criminal_id | Number (8) |
| criminal_alias | varchar2 (24) |
Table 4: Crimes
The table would be storing the crime details. The relationship between table criminals and crimes would be one to many as a criminal can do multiple crimes
| Column Name | Data type |
| criminal_id | Number(8) |
| crime_id | Number(8) |
| classification | Varchar2(40) |
| date_charged | Char(1) |
| appeal_status | Varchar2(40) |
| hearing_date | Date |
| appeal_date | Date |
| crime_codes | varchar2(240) |
| amount | number(12) |
| court_fee | number(12) |
| amount_paid | Char(1) |
| payment_due_date | Date |
| charge_ status | varchar2 (20) |
Table 5: Crime_arresting_officer
The table would store the details of arresting officers arresting a criminal for crime.
| Column Name | Data type |
| criminal_id | Number(8) |
| crime_id | Number(8) |
| officer_id | Number(8) |
Table 6: Sentence
The table would be storing the punishment details against a criminal for crime. The table would have one to one relation with table crime
| Column Name | Data type |
| criminal_id | Number(8) |
| crime_id | Number(8) |
| start_date | Date |
| end_date | Date |
| no_of_violations | Number(3) |
| sentences | Varchar2 (40) |
Table 6: Appeal
The table would have details for storing the details of a appeal against a sentence. For an appeal three appeals can be made and hence the relationship between sentence and appeal will be one to many.
| Column Name | Data type |
| criminal_id | Number(8) |
| crime_id | Number(8) |
| appeal_id | Number(8) |
| appeal_filling_date | Date |
| appeal_hearing date | Date |
| appeal_status | varchar2(40) |
Table 7: officer
The table will store the personal details of police officer.
| Column Name | Data type |
| officer_id | number(8) |
| officer name | varchar2(40) |
| officer_precinct | varchar2(40) |
| officer_badge no | varchar2(40) |
| officer_status | char(1) |
Table 8: officer_phone
The table would store the phone numbers of police officer.
| Column Name | Data type |
| officer_id | number(8) |
| officer_phone_no | number(12) |
References
Books and Journals
Teorey, T.J., Lightstone, S.S., Nadeau, T. and Jagadish, H.V., 2011.Database modeling and design: logical design. Elsevier.
De Simone, F., Tagliasacchi, M., Naccari, M., Tubaro, S. and Ebrahimi, T., 2010, March. A H. 264/AVC video database for the evaluation of quality metrics. In 2010 IEEE International Conference on Acoustics, Speech and Signal Processing (pp. 2430-2433). IEEE.
Ambler, S., 2012. Agile database techniques: Effective strategies for the agile software developer. John Wiley & Sons.
Punta, M., Coggill, P.C., Eberhardt, R.Y., Mistry, J., Tate, J., Boursnell, C., Pang, N., Forslund, K., Ceric, G., Clements, J. and Heger, A., 2011. The Pfam protein families database. Nucleic acids research, p.gkr1065.
Gupta, S., Castleman, K.R., Markey, M.K. and Bovik, A.C., 2010, May. Texas 3D face recognition database. In Image Analysis & Interpretation (SSIAI), 2010 IEEE Southwest Symposium on (pp. 97-100). IEEE.
Btechnd Assignment provide assignment writing service based on case study requirements in affordable prices and we are providing most flexible online assignment writing help, so book your Assignment with us, Order Now.
No comments:
Post a Comment