Databases integrity and safeguard its security

Assignment Help Database Management System

Reference no: EM133853179

Database Fundamentals

Assessment 1:

Introduction
In this assessment, you are required to write a report of no more than 1500 words. You need to create an ER diagram based on the case study given and construct a written specification. Book assignment help service now!

Case Study: Smart Traffic Management System (STMS)
The city of NeoMetro is implementing a Smart Traffic Management System (STMS) to optimize traffic flow, improve safety, and reduce congestion using AI-powered sensors and real-time analytics. The system will manage traffic signals, vehicle violations, and emergency routes while storing essential data for decision-making and planning.

Database Requirements:

Traffic Intersections: Each intersection has a unique ID, location, and traffic sensor data.
Traffic Signals: Stores signal timing and control settings at each intersection.
Vehicle Violations: Records violations, including license plate, type of violation, and penalty.
Emergency Routes: Manages priority access for ambulances and fire trucks.

Table Description:
INTERSECTION represents traffic junctions, each with a unique ID, location, sensor data to monitor congestion, and signal status for traffic control.
VEHICLE records passing vehicles, identified by their registration number, type (e.g., car, truck), and owner details.

VIOLATION_RECORD tracks traffic violations, linking each to a vehicle, specifying the type of offense, penalty, and time of occurrence.
EMERGENCY_ROUTE manages designated routes for emergency vehicles, linking them to intersections, specifying vehicle type, and updating clearance status.

Activity 1: Draw a detailed ER diagram
You can use the description in the case study for the ER diagram.

Activity 2: Write Specification from Entity Relationship Diagram
Compose the operational guidelines for the Entity-Relationship Diagram in activity 1. Exclude attribute descriptions and focus on specifying all cardinalities, participation constraints, and identifying strong and weak relationships. Please use plain English rather than technical database terminology.

The format structure for the report
You are required to follow the following structure for this assessment:
Introduction
Task 1
Task 2
Conclusion

Assessment 2:

Introduction
For this assignment, you will need to implement the normalization process on unstructured tables provided.

Activity 1: Normalisation
Create a relational schema and demonstrate the First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF) for the provided tables. When breaking down composite attributes like ‘full name,' make sure to select suitable attribute names, such as dividing it into ‘firstname' and ‘lastname.'

Activity 2: Normalisation
Create a relational schema and demonstrate the Third Normal Form (3NF) for the database and table design you had created in your first assignment.
When breaking down composite attributes like ‘full name,' make sure to select suitable attribute names, such as dividing it into ‘firstname' and ‘lastname.'

The format structure for the report
You are required to follow the following structure for this assessment. Below is the description for the report:
Introduction
Task 1
Task 2
Conclusion

Assessment Item 3

Introduction
For this assignment, you will need to construct a database, execute CRUD operations within the SQL database, and engage in a discussion concerning data security concerns.

Activity 1: SQL Queries (Case Study):
Activity 1 is the continuation for the assessment 1. Victorian Hospital is very pleased with your report and has approved the report. Based on the ER diagram created in assessment 1, you are to create the database.
Write SQL queries to create the table. Select suitable data types for each attribute and identify appropriate primary and foreign keys as needed.
Write SQL queries to insert some data into each table (At least 10 records).
Write the SQL queries for the tables created
Create 5 SQL queries.
Create a View.
Complexity of the query.
You are required to use a database tool to run the query. Screenshots of the query and result should be included in the report.

Activity 2: Database Administration
Examine the database established during Activity 1 and deliberate on the various forms of user access permissions necessary to uphold the database's integrity and safeguard its security. Additionally, explore the potential security concerns that could emerge if user inputs aren't adequately validated before being utilized as queries within the database. Support your analysis with relevant instances drawn from a case study.

Tools Required
For this assessment, you are expected to use from the following tools for the database:
10

SQL Server workbench
Postgresql
MS SQL Server
DBreaver
Microsoft Data Studio

The format structure for the report You are required to follow the following structure for this assessment. Below is the description for the report: • Introduction
Task 1
Task 2
Conclusion

Reference no: EM133853179

Questions Cloud

Write sql queries to insert some data into each table : Write SQL queries to create the table. Select suitable data types for each attribute and identify appropriate primary and foreign keys as needed

Explain core concepts related to corporate valuation : Explain core concepts related to corporate valuation and governance and to identify strategies for conducting business with personal and professional integrity.

Demonstrate the third normal form : You will need to implement the normalization process on unstructured tables provided - Create a relational schema and demonstrate the Third Normal Form

What is registration that a licensee-in-charge needs to hold : Officer in Effective Control (OIEC) / Principal Agent a. What is the minimum licence or registration that a licensee-in-charge needs to hold?

Databases integrity and safeguard its security : Examine the database established during Activity 1 and deliberate on the various forms of user access permissions necessary to uphold the database

What would the nurse teach the patient or her relatives : A patient had orthopedic surgery and prescribedWhat would the nurse teach the patient or her relatives about this low molecular weight heparin before discharge?

Implementing a smart traffic management system : Implementing a Smart Traffic Management System (STMS) to optimize traffic flow, improve safety, and reduce congestion using AI-powered sensors and real-time

Which action will nurse take when using sterile technique : The nurse is preparing to perform sterile dressing change for client with new abdominal incision. Which action will the nurse take when using sterile technique?

What observation made by the clinical instructor requires : What observation made by the clinical instructor requires intervention before the student is allowed to attend the rotation?

User Account

All Pages