Database Questions and Answers - PDF

Ok, here is the transcription of the text in the image to markdown format. ### 1. Database **(a)** State the data type for: 1. Species - String or text 2. Gradient - Integer **(b)** Outline one way that data validation could be carried out on the gradient attribute. - Data validation is...

Ok, here is the transcription of the text in the image to markdown format. ### 1. Database **(a)** State the data type for: 1. Species - String or text 2. Gradient - Integer **(b)** Outline one way that data validation could be carried out on the gradient attribute. - Data validation is checking to see if the data entered is within an appropiate range, for example to check that gradient is between -90 to +90. **(c)** Diagram as follows: | | | | | | | :---- | :------------------- | :------ | :-------------- | :---- | | PLANT | 1 has-a N | Distrubution | N has-a 1 | Site | | PLANT | →Distribution | | → Site | | **(d)** Outline why a composite primary key is used for the Distribution table. - The Plant ID and Site ID can be repeated. Many plants can be in a site and many sites can have plants. No one of the attributes can uniquely identify the record. --- ### 2. **(e)** Identify the steps to create a query to calculate the total number of sites where gorse has been found from the samples carried out on 14 october 2019. ```sql SELECT COUNT(SITE_ID) FROM PLANT, Distribution WHERE species= "Gorse" AND DATE = "14/10/2019" AND PLANT.Plant_ID = Distribution.PlantID ``` **(a)** Identify two roles of the database administrator Identify the steps that take place in a transaction when a parent attempts to pay for the school bus at end of a month. - Parent authenticated by the DBMS. The amount/Bill is then being calculated and displayed. Transaction is initiated for the transport payment. Payment details are added for transport. If payment details and payment can be processed by DBMS, then school transport account is credited. **(f)** Explain how data consistency can be maintained in the Environment database. - Any data can be maintained by using data validation such as range checks for numerical values like gradient. Referential integrity ensures that species ID's in the plant table match valid records. Normalization eliminates data redundancy by organizing information across multiple related tables. Regular data updates and constraints prevent incorrect or duplicate entries. --- ### 3. **(b)** Explain how the database management system (DBMS) prevents a record being updated by two parents simultaneously. - DBMS uses record locking, Isolation. Data locking to ensure exclusive editing it is done in isolation to prevent data values from being accessed by a transaction while another transaction involving the same data is in progress. **(c)** Identify two roles of the database administration at Bucharesti School - Approving Data Access - Implementing security **(d)** Outline two ways that a database management system (DBMS) can be used to ensure the students Personal data remains secure. - Access controls: Effective and effiecient access control so that the end users can access only the data or program for which they have privilege for. - Delta Encryption : Ensure the confidentiality of Data. **(e)** Explain how the data developed of the Bucharesti School database can ensure that it has been designed ethically - Developers of a school database can ensure ethical design buy implementing strict data privacy measures, such as encryption and Access controls, to protect student information. Transparent data collection policies and obtaining consent from shareholders are also essential. Ensuring that the inappropriate use of data cannch take place for example sharing the data with third parties without the consent of Data subject. --- ### 4. **(a)** Outline one reason why database are normalized - Reduces duplicated or removes redundant data, meaning it reduces wastage of storage space **(b)** Outline why the data type for the olympic Record attribute (OlymRec) cannot be an integer. -The value in OlymRec has a decimal point so it would not be accurate data type is required **(c)** Construct the 2nd Normal Form (2NF) of the unnormalized ATHLETICS relation shown stih cibove. - INF-Unique PK Athletics (Event ID, Even, type, subtype, gender, OlymRec,Wid - INE-Composite PK ATHLETICS (Events Type, Subtype, Gender, OlymRec, Wid Rec) - 2NF-Composite PK EVENTS (Even, type, Subtype) ATHLETICS (Event, Gender, OlymRec, WidRec) EVENTS ( EventIID, Event, Type, subtype) Athletics (EventID, gender, OlymRec, WidRec --- ### 5. **(d)** Outline why databases are normalized from 2nd normal form C2NF) to 3rd normell form (3NF) To remove transitive non-key dependences; where non-key attribute(s) depend on another non-hey attribute and further - reduce data redundancy: