SQL Exercises

Assalamualaikum and Hello Students;

Please download this file SQL EXERCISE 1

We will do this exercises during class.

Thank you

Madam Munirah Abdullah

Advertisements

Ralat Lab 4

Asssalamualaikum & Hello

PEMBETULAN UNTUK LAB 4 (SQL)
BAHAGIAN ACTIVITY
Step 5 : Alter the EMPLOYEE table by changing the data type of the column namedEMP_PHONENO CHAR(4) to EMP_PHONENO INT(11).
ALTER TABLE EMPLOYEE
MODIFY EMP_PHONENO INT(11);
tukar kepada
ALTER TABLE EMPLOYEE
MODIFY EMP_PHONENO NUMBER(11)
Step 6 : Alter the EMPLOYEE table by changing the column name to the new nameof columnEMP_PHONENO INT to EMP_PHONE INT.
ALTER TABLE EMPLOYEE
CHANGE EMP_PHONENO EMP_PHONE INT(11);
tukar kepada
ALTER TABLE EMPLOYEE
RENAME COLUMN EMP_PHONENO TO EMP_PHONE
BAHAGIAN EXERCISE
Data Dictionary for the tables is as below:
Tables Name
Attributes Name
Data Types
PK/FK
FK refer to
STUDENT
StudID
VARCHAR(5)
PK
StudName
VARCHAR(20)
StudAddress
VARCHAR(30)
StudBirthDate
DATE
CourseID
VARCHAR(5)
FK
COURSE
COURSE
CourseID
VARCHAR(5)
PK
CourseName
VARCHAR(10)
LectID
VARCHAR(5)
FK
LECTURER
LECTURER
LectID
VARCHAR(5)
PK
LectName
VARCHAR(20)
LectTelNo
NUMBER(10)
DepartID
VARCHAR(5)
FK
DEPARTMENT
DEPARTMENT
DepartID
VARCHAR(5)
PK
DepartName
VARCHAR(30)
SUBJECT
SubID
VARCHAR(5)
PK
SubName
VARCHAR(20)
STUD_SUB
StudSubID
VARCHAR(5)
PK
StudID
VARCHAR(5)
FK
STUDENT
SubID
VARCHAR(5)
FK
SUBJECT
Mark
NUMBER(3)
Grade
CHAR(2)
1.Add one column StudTelNo in STUDENT table and the data type is NUMBER(9). (2m)
Thank you

THREE SCHEMA ARCHITECTURE

Figure2_4

Data and Related Structures

Data are actually stored as bits, or numbers and strings, but it is difficult to work with data at this level.

It is necessary to view data at different levels of abstraction.

Schema:

  • Description of data at some level. Each level has its own schema.

We will be concerned with three forms of schemas:

  • physical,
  • conceptual, and
  • external.

Physical Data Level

The physical schema describes details of how data is stored: files, indices, etc. on the random access disk system.  It also typically describes the record layout of files and type of files (hash, b-tree, flat).

Early applications worked at this level – explicitly dealt with details.

E.g., minimizing physical distances between related data and organizing the data structures within the file (blocked records, linked lists of blocks, etc.)

Problem:

  • Routines are hardcoded to deal with physical representation.
  • Changes to data structures are difficult to make.
  • Application code becomes complex since it must deal with details.
  • Rapid implementation of new features very difficult.

Conceptual Data Level

Also referred to as the Logical level

Hides details of the physical level.

  • In the relational model, the conceptual schema presents data as a set of tables.

The DBMS maps data access between the conceptual to physical schemas automatically.

  • Physical schema can be changed without changing application:
  • DBMS must change mapping from conceptual to physical.
  • Referred to as physical data independence

External Data Level

In the relational model, the external schema also presents data as a set of relations. An external schema specifies a view of the data in terms of the conceptual level. It is tailored to the needs of a particular category of users. Portions of stored data should not be seen by some users and begins to implement a level of security and simplifies the view for these users

Examples:

  • Students should not see faculty salaries.
  • Faculty should not see billing or payment data.

Information that can be derived from stored data might be viewed as if it were stored.

  • GPA not stored, calculated when needed.

Applications are written in terms of an external schema. The external view is computed when accessed.  It is not stored. Different external schemas can be provided to different categories of users. Translation from external level to conceptual level is done automatically by DBMS at run time. The conceptual schema can be changed without changing application:

  • Mapping from external to conceptual must be changed.
  • Referred to as conceptual data independence.

[SOURCES :  3 LEVEL DATABASE ARCHITECTURE]

<<DOWNLOAD EXTRA NOTES : lec.arch1.2up >>

<<DOWNLOAD EXTRA NOTES :L02>>

Welcome Back!!

To new students welcome to PUO
To old students welcome back!!

All the best for this new semester 😀

Education is the best friend. An educated person is respected everywhere. Education beats the beauty and the youth.

Chanakya