Page 92 - Computer Software Application TP - Volume 1
P. 92

COMPUTER SOFTWARE APPLICATION - CITS




           EXERCISE 24 :  Enforcing Constraints, Primary Key and
                                       Foreign Key


            Objectives

            At the end of this exercise you shall be able to
           •  connect the MySQL database server
           •  design the table
           •  create primary & foreign key

           Requirements

           Tools/Materials
           •   Desktop/Laptop with latest configuration
           •   Operating system: window 10:11
           •  XAMPP server r3.3.0

           Procedure

           TASK 1 : Access MySQL:
           MySQL Workbench: Download and install the user-friendly GUI for database management.

           Command Prompt: Open the Command Prompt and navigate to the MySQL installation directory (e.g., C:\Program
           Files\MySQL\MySQL Server 8.0\bin).
           Connect to your Database:

           MySQL Workbench: Enter your server hostname, username, password, and database name to connect.
           Command Prompt: Use the mysql command followed by your credentials (e.g., mysql -h localhost -u root -p).

           TASK 2 : Design Your Tables:
           1  Primary Key: Identify the unique identifier for each table. This column should not allow null values and should
              have a unique constraint applied:
             product id INT PRIMARY KEY AUTO INCREMENT,
             name VARCHAR(255) NOT NULL UNIQUE,
             price DECIMAL(8,2) NOT NULL

           );
           Establish Relationships:
           2  Foreign Key: Define references between related tables. The referencing column must match the data type of
              the referenced column:
           CREATE TABLE orders (
             order id INT PRIMARY KEY AUTO INCREMENT,
             customer id INT NOT NULL,
             product id INT NOT NULL,
             FOREIGN KEY (customer id) REFERENCES customers(customer id),

             FOREIGN KEY (product id) REFERENCES products(product id)
           ON DELETE SET NULL
           );




                                                           77
 CITS : IT & ITES - Computer Software Application - Exercise 23
   87   88   89   90   91   92   93   94   95   96   97