Computer Science, IT, Database design and development assignment, Essays (high school) of Computer science

Download Computer Science, IT, Database design and development assignment and more Essays (high school) Computer science in PDF only on Docsity!

UNIT 4 DATABASE DESIGN & DEVELOPMENT

Assignment 2

HND 36

Thel Thel Thitsar

Contents

• SQL
• DDL and DML..............................................................................................
• Database Development
• Data Manipulation and Extraction
• Trigger Function
• Database (Backup)
• Restore Database
• Database Security
• Database Security Steps
• Testing in Database
• Test Plan
• Error Messages
• Effectiveness of Testing

Above picture shows the Brand Table in SQL. It has two columns, BrandID and BrandName. The first one is Primary Key using varchar datatype. The last one is varchar (20). Allows the user to insert Brandname up to 20 characters.

Categoty Table

Above picture shows the Category Table in SQL. It has two columns, CategoryID and

SubCategory. The first one is Primary Key and varchar datatype. Last one is Varchar

(10) and user can insert the SubCategory Name up to 10 as necessarily.

Product Table

Above picture is the Product Table and the constraint primary key is assigned to the

ProductID. And the column named ‘Discount’ is as varchar datatype and default 0.

There are a total two implementations of Foreign Key. They are CategoryID and

BrandID.

Customer Table

Above picture shows the Customer Table and CustomerID is assigned as Primary Key.

And all the column were give as varchar datatype. Also I put check constraints too.

There is check constraint that check for Customer ID like ‘CT[0-9]’. This means the

This is the Delivery Staff Table and DeliveryStaffID is assigned as Priamry Key. It has three columns. All are given as varchar datatype. Constraint for DeliveryStaffID like ‘DS[09][0-9]’.

Office Table

Above picture shows the Office Table. It has five columns and all are given as varchar datatype. OfficeID is assigned as Primary Key. There are three check constraints that I added. Check constraint for OfficeID like ‘0[0-9]’. Check constraint for OfficeEmail like %@gmail.com and check constraint for Department like (Department in (‘Sale & Marketing’)).

Order Record Table

Above picture shows the Order Record Table and it has eleven columns. All are varchar datatype. OrderNumer is assigned as primary key. There are total of four foreign keys and they are StaffID, DeliveryStaffID, CustomerID and OfficeID.

Order Details

Like

Order By

Select

Count

Trigger Function

Triggers are procedures stored in the database that are automatically requested when a special event occurs in the database. For example, a trigger can be called when a row is entered into a specified table, or when a specific column in a table is updated.

Database developers can use triggers to define specific actions. Regardless of which program or user changed the data, it processes the observed file.

Table for Trigger

Above picture shows the table for Trigger that I created.

Insert Trigger

Above picture shows the Insert Trigger on Brand Table.

Delete Trigger

Above picture shows the delete trigger on Brand Table.

Update Trigger

Above picture shows the process of database backup. Select the database that you want to back up and right click on this. Then tap ‘Tasks’ and another panel will appears. Select the ‘Backup’.

The above windows will appears and you can select your database name and the system will automatically select backup file location. If you want to change the location of backup file you can remove the current location and you can add new by clicking ‘Add’.

And the above picture will appear when the backup process is successfully completed.

If the display file is the correct file, you can click ‘OK’. Then, the database restore process is finished.

Database Security

Database Security, contains a number of security measures to protect the database management system (DBMS). The types of data security enterprises that a company must use include protecting the sub-infrastructure including databases (servers and networks, etc.), DBMS security configuration, and data access.

Data Security and Controls

Many of the controls include database hardening, availability, database configuration, and security monitoring. You can use these various security controls to manage your security loop.

Hardening and Monitoring System

The basic architecture provides additional access to the DBMS. It is very important to strengthen known security configuration standards, keep all systems up to date, and monitor access containing internal threats.

DBMS Configuration

It is important to properly configure and apply the DBMS to harden security and restore privileges, including misconfigurations with pending security settings. By controlling the configuration of the DBMS and ensuring proper process control, you can maintain configuration consistency.

Authentication

Authentication is one of the data protection measures. In the process, we systematically verify that the user's credentials are consistent with those stored in the database and ensure that only verified users have access to the data network and database.

Access

An inalienable consequence of database security is effectively restricting access to data. Access controls the authentication of legitimate users and applications and restricts access to them in the database. This includes developing custom characteristics and functionality suitable for access, as well as restrictions on deployment and administration rights.

Above picture shows the when security process starts. The user have to click on security and then click on Login

Then ‘new log in’ panel will appear.

Above photos show the display when the user click the new login. And user have to add user name and Password.