What is an "School database management system" ?
Database management system plays a major role in a school. It connects data of all the main factors which support for run a school.
The DBMS helps us to manage and execute our day-to-day academic and administrative purposes and provide a better academic experience for students.
Mainly it helps to maintain security, institute's utilities, and real- time updates.
Main parts of a DBSM
In this sector we talk about the main important sectors of DBMS.
What is an entity in DBSM ?
An entity is an object in the real world with an independent existence and can be differentiated from other objects. In database administration, an entity can be a single thing, person, place, or object. Data can be stored about such entities. Examples of an entity are a single person, single product, or single organization. An Entity Type defines a collection of similar entities . An Entity Set is a collection of entities of an entity type at a point of time. There are two main types of entities. They are strong entity and weak entity. In this ER diagrams, an entity type is represented by a name in a box. For example, some entities of a school database are teacher , student , parent , Prefect , Non-academic staff etc.
What is Attribute in DBMS?Attributes describe the characteristics or properties of an entity in a database table. An entity in a database table is defined with the ‘fixed’ set of attributes.
Let’s look at the example of a university management system.
The student entity has attributes such as student id, name, age, address, GPA.
In the diagram, each attribute is represented by an oval with a name inside.
· Also, an entity is a row or a record in the table whereas an attribute is a column in the table. Observe the figure below it shows the attributes of the student table
Types of Attributes
In ER diagram, attributes associated with an entity set may be of the following types-
1. Simple attributes
2. Composite attributes
3. Multi valued attributes
4. Derived attributes
5. Key attributes
Attributes describe the characteristics or properties of an entity in a database table. An entity in a database table is defined with the ‘fixed’ set of attributes.
Let’s look at the example of a university management system.
The student entity has attributes such as student id, name, age, address, GPA.
In the diagram, each attribute is represented by an oval with a name inside.
· Also, an entity is a row or a record in the table whereas an attribute is a column in the table. Observe the figure below it shows the attributes of the student table
Types of Attributes
In ER diagram, attributes associated with an entity set may be of the following types-
1. Simple attributes
2. Composite attributes
3. Multi valued attributes
4. Derived attributes
5. Key attributes
1. Simple Attributes
Simple attributes are those attributes which can not be divided further.
2. Composite Attributes-
Here, the attributes “Name” and “Address” are composite attributes as they are composed of many other simple attributes.
3. Multi Valued Attributes-
Multi valued attributes are those attributes which can take more than one value for a given entity from an entity set.
Here, the attributes “Mob_no” and “Email_id” are multi valued attributes as they can take more than one values for a given entity.
4. Derived Attributes
Derived attributes are those attributes which can be derived from other attribute(s).
Here, the attribute “Age” is a derived attribute as it can be derived from the attribute “DOB”.
5. Key Attributes-
Key attributes are those attributes which can identify an entity uniquely in an entity set.
Here, the attribute “Roll_no” is a key attribute as it can identify any student uniquely.
What do "Relationships" do?
Relationships are the glue that holds the tables together. They are used to connect related information between tables. A relationship , in the context of database, is a situation that exists between two relational database tables when one table has a foreign key that references the primary key of the other table. Relationship strength is based on how the primary key of a related entity is defined. A week, or non-identifying, relationship exists if the primary key.
Types of relationships
i)One to many Relationship ● A one to many relationship should be the norm in any relational database design and is found in all relational database environments.
ii)One to one Relationship ● A one to one relationship is the relationship of one entity to only one other entity, and vice versa. It should be rare in any relational database design. In fact, it could indicate that two entities actually belong in the same table.
iii)Many to many Relationship ● A many to many relationship occurs when multiple records in a table are associated with multiple records in another table.
ER Diagram With Assumption and explanation
Identification of actors
Identifying actors is one of the first steps in use case analysis. Each type of external entities with which the system must interact is represented by an actor. For example, the school system consists of the users, devices, and programs that the system interacts with. These are called actors which has the following characteristics.
An actor in use case modeling specifies a role played by a user or any other system that interacts with the subject.
An Actor models a type of role played by an entity that interacts with the subject (e.g., by exchanging signals and data), but which is external to the subject.
Actors may represent roles played by human users, external hardware, or other subjects.
Actors do not necessarily represent specific physical entities but merely particular facets of some entities that are relevant to the specification of its associated use cases.
A single physical instance may play the role of several different actors and a given actor may be played by multiple different instances.
Types of actors include:
· Users
· database systems
· clients and servers
· cloud platforms
· devices
What is a Business Process?
We have all heard the term process before, but what exactly does it mean? A process is a series of tasks that are completed in order to accomplish a goal. A business process, therefore, is a process that is focused on achieving a goal for a business. If you have worked in a business setting, you have participated in a business process. Anything from a simple process for making a sandwich at Subway to building a space shuttle utilizes one or more business processes.
Processes are something that businesses go through every day in order to accomplish their mission. The better their processes, the more effective the business. Some businesses see their processes as a strategy for achieving competitive advantage. A process that achieves its goal in a unique way can set a company apart. A process that eliminates costs can allow a company to lower its prices (or retain more profit).
v Why every school needs better business process management?
A "business process" is management-speak for a series of tasks which have an element of complexity. Hiring teachers, curriculum planning or budgeting are examples of business processes common to all schools and they happen every year.
Other examples are specific to school type - so academy conversion is a business process, and so is billing and fee collection for independent or state boarding schools. In every case, applications that manage data are the key to success.
v Examples of business process
· sharing the sales proposal.
· sending quotes.
· negotiations.
· receiving orders for product/service.
· updating records of sales.
· delivery of product/service.
· billing.
· payment
Functional Requirements of a system
v A School Management System not only gives information about students. It can provide real-time information about teacher’s activities also. It also provides a dedicated portal for teachers making it easier for them to find all information related to students and school activities.
Now let us take a look at why institutes need to implement this system.
Ø Student Information
Ø Parent Access
Ø Teacher Information
Ø SMS and Real-time app notification
Ø Attendance Management
Ø Report Cards
Ø Fee Management and Online Payments
- Functional Requirements Of School
Now we can mention all the functional requirements for school management system and define some of them. The functional requirements for school management system are given below ;
· Student shall give test
· Student shall communicate the teacher
· Teacher shall login
· Teacher shall communicate with admin, student and parents
· Teacher shall assign assignments to the student
· Teacher shall collect assignment of his student.
· Teacher shall take test online
· Teacher shall mark attendance online
· Parents shall login through his child id
1. Non-functional requirement of the system.
i. Security System.
v System would be able to choose and provide facilities only for Authorized
ii. Scalability.
iii. Ease of use.
iv. Accessibility.
v. Maintainability.
vi. Performance.
v Serve could be able to desire tasks in reasonable unit of time
vii. Reliability
viii. Efficient System.
ix. More roles and more features.
Normalization of the Relation drawn using the ER Diagram
Entity relationship diagram is one of the most widely used technique for data modeling. Data modeling is an essential component of database design and development. During the normalization process of database design, make sure that proposed entities meet required normal from before table structures are created. Many real – world database have been improperly designed or burdened with anomalies if improperly modified. This can be a large undertaking if the tables are not properly normalized. Good data models can make a significant contribution to an organization’s future success.
Data modeling is performed during the initial phases of the database development process as shown in figure. During this process, the top two phases are concerned with the information content of the database, while the last two phases are concerned with the implementation of the database onesome commercial DBMS.
During the conceptual data modeling phase, data requirements are expressed through an ERD. The conceptual data modeling phase in general is independent of a DBMS. This phase may also enhance or refine the data model (ERD) of the previous phase to ensure efficient utilization of the database. Since most of the commercial DBMS are based on the relational model, the end product of this phase is relational model design.
Normalization utilizes association among attributes within an entity table to accomplish its objective. Since an ERD also utilizes association among attributes as a basis to identity entity type structure, it is possible to apply normalization during ERD development.
Relational Database schema with foreign key references
v Foreign key DBMS
A foreign key is different from a super key, candidate key or primary key because a foreign key is the one that is used to link two tables together or create connectivity between the two.
v What is a foreign key
A foreign key is the one that is used to link two tables together via the primary key. It means the columns of one table points to the primary key attribute of the other table. It further means that if any attribute is set as a primary key attribute will work in another table as a foreign key attribute. But one should know that a foreign key has nothing to do with the primary key.
v Use of foreign key
The use of a foreign key is simply to link the attributes of two tables together with the help of a primary key attribute. Thus, it is used for creating and maintaining the relationship between the two relations.
v Example of foreign key
Consider two tables Student and Department having their respective attributes as shown in the below table structure:
In the tables, one attribute, you can see, is common, that is Stud_Id, but it has different key constraints for both tables. In the Student table, the field Stud_Id is a primary key because it is uniquely identifying all other fields of the Student table. On the other hand, Stud_Id is a foreign key attribute for the Department table because it is acting as a primary key attribute for the Student table. It means that both the Student and Department table are linked with one another because of the Stud_Id attribute.
In the below-shown figure, you can view the following structure of the relationship between the two tables.
13. SQL statements on table creation and populating them (CREATE and INSERT statements).
CREATE TABLE Statement
The general format for the CREATE TABLE command is:
CREATE TABLE <tablename>
(
ColumnName, Datatype, Optional Column
Constraint,
ColumnName, Datatype, Optional Column
Constraint,
Optional table Constraints
);
Tablename is the name of the database table such as Employee. Each field in the CREATE TABLE has three parts (see above):
• ColumnName
• Data type
• Optional Column Constraint
I. ColumnName
The ColumnName must be unique within the table. Some examples of ColumnNames are FirstName and LastName.
Data Type
The data type, as described below, must be a system data type or a userdefined data type. Many of the data types have a size such as CHAR (35) or Numeric (8, 2).
1. Bit –Integer data with either a 1 or 0 value 183
2. Int –Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 – 1 (2,147,483,647)
3. Smallint–Integer data from 2^15 (-32,768) through 2^15 – 1 (32,767)
4. Tinyint–Integer data from 0 through 255
5. Decimal –Fixed precision and scale numeric data from -10^38 -1 through 10^38
6. Numeric –A synonym for decimal
7. Timestamp –A database-wide unique number
8. Uniqueidentifier –A globally unique identifier (GUID)
9. Money - Monetary data values from -2^63 (- 922,337,203,685,477.5808) through 2^63 – 1 (+922,337,203,685,477.5807), with accuracy to one-tenthousandth of a monetary unit
10. Smallmoney –Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to one-ten-thousandth of a monetary unit
11. Float –Floating precision number data from -1.79E + 308 through 1.79E + 308
12. Real –Floating precision number data from -3.40E + 38 through 3.40E + 38
13. Datetime –Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one-three-hundredths of a second, or 3.33 milliseconds
14. Smalldatetime –Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute
15. Char –Fixed-length non-Unicode character data with a maximum length of 8,000 characters
16. Varchar –Variable-length non-Unicode data with a maximum of 8,000 characters
17. Text –Variable-length non-Unicode data with a maximum length of 2^31 – 1 (2,147,483,647) characters
18. Binary –Fixed-length binary data with a maximum length of 8,000 bytes 184
19. Varbinary –Variable-length binary data with a maximum length of 8,000 bytes
20. Image – Variable-length binary data with a maximum length of 2^31 – 1 (2,147,483,647) bytes
Optional Column Constraints
The Optional Column Constraints are NULL, NOT NULL, UNIQUE, PRIMARY KEY and DEFAULT, used to initialize a value for a new record. The column constraint NULL indicates that null values are allowed, which means that a row can be created without a value for this column. The column constraint NOT NULL indicates that a value must be supplied when a new row is created. To illustrate, we will use the SQL statement CREATE TABLE EMPLOYEES to create the students table with 6 attributes or fields.
USE university
CREATE TABLE Students
(
StudentRegNo CHAR(10) NOT NULLUNIQUE,
StudentFNameCHAR(30)NOT NULL,
StudentMName CHAR(25)NOT NULL,
StudentLName CHAR(25)NOT NULL, Y
earEnrolledDATE NOT NULL,
BirthDate DATE NOT NULL,
CONSTRAINT students_PK PRIMARY
KEY(StudentRegNo
);
The first field is ‘StudentRegNo’ with a field type of CHAR. For this field, the field length is 10 characters, and the user cannot leave this field empty (NOT NULL).
Similarly, the second field is StudentFName with a field type CHAR of length 30. After all the table columns are defined, a table constraint, identified by the word CONSTRAINT, is used to create the primary key:
CONSTRAINT StudentsPK PRIMARY KEY(StudentRegNo)
We will discuss the constraint property further later in this unit. Likewise, we can create two more tables a ‘course’ table and ‘AcademicProgram’ table using the CREATE TABLE SQL DDL command as shown in the below example.
USE university
CREATE TABLE COURSE
(
CourseTitle Char(35) NOT NULL,
CourseCode Char(30) NOT NULL,
CourseUnitsInt NOT NULL,
CONSTRAINT COURSE_PK PRIMARY
KEY(CourseCode)
);
In this example, an ‘AcademicProgram’ table is created with three fields: ProgramID, ProgramName and CreditPoints.
USE university
CREATE TABLE AcademicPrograms
(
ProgramIDInt NOT NULL IDENTITY,
ProgramNameChar(50) NOT NULL,
CreditPointsInt NOT NULL,
CONSTRAINT AcademicPrograms_PK PRIMARY KEY(ProgramID)
);
INSERT INTO Statement
The INSERT INTO statement is used to insert new records in a table.
It is possible to write the INSERT INTO statement in two ways:
1. Specify both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:
INSERT INTO table_Name
VALUES (value 1, value 2 , value3 , …..);
Example
The following statements would create six records in the CUSTOMERS table.
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES ( 1 , “Ramesh” , 32 , “Ahmedabad” , 2000.00 );
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (2, “Khilan”, 25 , “Delhi” , 1500.00 );
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES ( 3 , “Kaushik” , 23, “Kota” , 2000.00 );
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES( 4, “Chaitali” , 25, “Mumbai” , 6500.00 );
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES( 5. “Hardik” , 27 , “Bhopal ” , 8500.00 );
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES( 6, “Komal , 22, “MP” , 4500.00 );
You can create a record in the CUSTOMERS table by using the second syntax as shown below.
INSERT INTO CUSTOMERS
VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );
All the above statements would produce the following records in the CUSTOMERS table as shown below.
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
MP
4500.00
7
Muffy
24
Indore
10000.00
Relational Database schema with foreign key references
v Foreign key DBMS
A foreign key is different from a super key, candidate key or primary key because a foreign key is the one that is used to link two tables together or create connectivity between the two.
v What is a foreign key
A foreign key is the one that is used to link two tables together via the primary key. It means the columns of one table points to the primary key attribute of the other table. It further means that if any attribute is set as a primary key attribute will work in another table as a foreign key attribute. But one should know that a foreign key has nothing to do with the primary key.
v Use of foreign key
The use of a foreign key is simply to link the attributes of two tables together with the help of a primary key attribute. Thus, it is used for creating and maintaining the relationship between the two relations.
v Example of foreign key
Consider two tables Student and Department having their respective attributes as shown in the below table structure:
In the tables, one attribute, you can see, is common, that is Stud_Id, but it has different key constraints for both tables. In the Student table, the field Stud_Id is a primary key because it is uniquely identifying all other fields of the Student table. On the other hand, Stud_Id is a foreign key attribute for the Department table because it is acting as a primary key attribute for the Student table. It means that both the Student and Department table are linked with one another because of the Stud_Id attribute.
In the below-shown figure, you can view the following structure of the relationship between the two tables.
Relational Database schema with foreign key references
v Foreign key DBMS
A foreign key is different from a super key, candidate key or primary key because a foreign key is the one that is used to link two tables together or create connectivity between the two.
v What is a foreign key
A foreign key is the one that is used to link two tables together via the primary key. It means the columns of one table points to the primary key attribute of the other table. It further means that if any attribute is set as a primary key attribute will work in another table as a foreign key attribute. But one should know that a foreign key has nothing to do with the primary key.
v Use of foreign key
The use of a foreign key is simply to link the attributes of two tables together with the help of a primary key attribute. Thus, it is used for creating and maintaining the relationship between the two relations.
v Example of foreign key
Consider two tables Student and Department having their respective attributes as shown in the below table structure:
In the tables, one attribute, you can see, is common, that is Stud_Id, but it has different key constraints for both tables. In the Student table, the field Stud_Id is a primary key because it is uniquely identifying all other fields of the Student table. On the other hand, Stud_Id is a foreign key attribute for the Department table because it is acting as a primary key attribute for the Student table. It means that both the Student and Department table are linked with one another because of the Stud_Id attribute.
In the below-shown figure, you can view the following structure of the relationship between the two tables.
Screen shots of My SQL workbench (SQL commands and the results set)
Few SELECT, UPDATE & DELETE queries executed on the tables explaining the result
project by - E.D.E. Kumarasinghe - s92086063
- R.W.S. Dileka - s92085639
- K.A.P.R. Edirisinghe - s92082502
- P.P.N.B. Gunasena. - s92085343
Identification of actors
Identifying actors is one of the first steps in use case analysis. Each type of external entities with which the system must interact is represented by an actor. For example, the school system consists of the users, devices, and programs that the system interacts with. These are called actors which has the following characteristics.
An actor in use case modeling specifies a role played by a user or any other system that interacts with the subject.
An Actor models a type of role played by an entity that interacts with the subject (e.g., by exchanging signals and data), but which is external to the subject.
Actors may represent roles played by human users, external hardware, or other subjects.
Actors do not necessarily represent specific physical entities but merely particular facets of some entities that are relevant to the specification of its associated use cases.
A single physical instance may play the role of several different actors and a given actor may be played by multiple different instances.
Types of actors include:
· Users
· database systems
· clients and servers
· cloud platforms
· devices
What is a Business Process?
We have all heard the term process before, but what exactly does it mean? A process is a series of tasks that are completed in order to accomplish a goal. A business process, therefore, is a process that is focused on achieving a goal for a business. If you have worked in a business setting, you have participated in a business process. Anything from a simple process for making a sandwich at Subway to building a space shuttle utilizes one or more business processes.
Processes are something that businesses go through every day in order to accomplish their mission. The better their processes, the more effective the business. Some businesses see their processes as a strategy for achieving competitive advantage. A process that achieves its goal in a unique way can set a company apart. A process that eliminates costs can allow a company to lower its prices (or retain more profit).
v Why every school needs better business process management?
A "business process" is management-speak for a series of tasks which have an element of complexity. Hiring teachers, curriculum planning or budgeting are examples of business processes common to all schools and they happen every year.
Other examples are specific to school type - so academy conversion is a business process, and so is billing and fee collection for independent or state boarding schools. In every case, applications that manage data are the key to success.
v Examples of business process
· sharing the sales proposal.
· sending quotes.
· negotiations.
· receiving orders for product/service.
· updating records of sales.
· delivery of product/service.
· billing.
· payment
v A School Management System not only gives information about students. It can provide real-time information about teacher’s activities also. It also provides a dedicated portal for teachers making it easier for them to find all information related to students and school activities.
Now let us take a look at why institutes need to implement this system.
Ø Student Information
Ø Parent Access
Ø Teacher Information
Ø SMS and Real-time app notification
Ø Attendance Management
Ø Report Cards
Ø Fee Management and Online Payments
- Functional Requirements Of School
Now we can mention all the functional requirements for school management system and define some of them. The functional requirements for school management system are given below ;
· Student shall give test
· Student shall communicate the teacher
· Teacher shall login
· Teacher shall communicate with admin, student and parents
· Teacher shall assign assignments to the student
· Teacher shall collect assignment of his student.
· Teacher shall take test online
· Teacher shall mark attendance online
· Parents shall login through his child id
1. Non-functional requirement of the system.
i. Security System.
v System would be able to choose and provide facilities only for Authorized
ii. Scalability.
iii. Ease of use.
iv. Accessibility.
v. Maintainability.
vi. Performance.
v Serve could be able to desire tasks in reasonable unit of time
vii. Reliability
viii. Efficient System.
ix. More roles and more features.
Normalization of the Relation drawn using the ER Diagram
Entity relationship diagram is one of the most widely used technique for data modeling. Data modeling is an essential component of database design and development. During the normalization process of database design, make sure that proposed entities meet required normal from before table structures are created. Many real – world database have been improperly designed or burdened with anomalies if improperly modified. This can be a large undertaking if the tables are not properly normalized. Good data models can make a significant contribution to an organization’s future success.
Data modeling is performed during the initial phases of the database development process as shown in figure. During this process, the top two phases are concerned with the information content of the database, while the last two phases are concerned with the implementation of the database onesome commercial DBMS.
During the conceptual data modeling phase, data requirements are expressed through an ERD. The conceptual data modeling phase in general is independent of a DBMS. This phase may also enhance or refine the data model (ERD) of the previous phase to ensure efficient utilization of the database. Since most of the commercial DBMS are based on the relational model, the end product of this phase is relational model design.
Relational Database schema with foreign key references
v Foreign key DBMS
A foreign key is different from a super key, candidate key or primary key because a foreign key is the one that is used to link two tables together or create connectivity between the two.
v What is a foreign key
A foreign key is the one that is used to link two tables together via the primary key. It means the columns of one table points to the primary key attribute of the other table. It further means that if any attribute is set as a primary key attribute will work in another table as a foreign key attribute. But one should know that a foreign key has nothing to do with the primary key.
v Use of foreign key
The use of a foreign key is simply to link the attributes of two tables together with the help of a primary key attribute. Thus, it is used for creating and maintaining the relationship between the two relations.
v Example of foreign key
Consider two tables Student and Department having their respective attributes as shown in the below table structure:
In the tables, one attribute, you can see, is common, that is Stud_Id, but it has different key constraints for both tables. In the Student table, the field Stud_Id is a primary key because it is uniquely identifying all other fields of the Student table. On the other hand, Stud_Id is a foreign key attribute for the Department table because it is acting as a primary key attribute for the Student table. It means that both the Student and Department table are linked with one another because of the Stud_Id attribute.
In the below-shown figure, you can view the following structure of the relationship between the two tables.
13. SQL statements on table creation and populating them (CREATE and INSERT statements).
CREATE TABLE Statement
The general format for the CREATE TABLE command is:
CREATE TABLE <tablename>
(
ColumnName, Datatype, Optional Column
Constraint,
ColumnName, Datatype, Optional Column
Constraint,
Optional table Constraints
);
Tablename is the name of the database table such as Employee. Each field in the CREATE TABLE has three parts (see above):
• ColumnName
• Data type
• Optional Column Constraint
I. ColumnName
The ColumnName must be unique within the table. Some examples of ColumnNames are FirstName and LastName.
Data Type
The data type, as described below, must be a system data type or a userdefined data type. Many of the data types have a size such as CHAR (35) or Numeric (8, 2).
1. Bit –Integer data with either a 1 or 0 value 183
2. Int –Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 – 1 (2,147,483,647)
3. Smallint–Integer data from 2^15 (-32,768) through 2^15 – 1 (32,767)
4. Tinyint–Integer data from 0 through 255
5. Decimal –Fixed precision and scale numeric data from -10^38 -1 through 10^38
6. Numeric –A synonym for decimal
7. Timestamp –A database-wide unique number
8. Uniqueidentifier –A globally unique identifier (GUID)
9. Money - Monetary data values from -2^63 (- 922,337,203,685,477.5808) through 2^63 – 1 (+922,337,203,685,477.5807), with accuracy to one-tenthousandth of a monetary unit
10. Smallmoney –Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to one-ten-thousandth of a monetary unit
11. Float –Floating precision number data from -1.79E + 308 through 1.79E + 308
12. Real –Floating precision number data from -3.40E + 38 through 3.40E + 38
13. Datetime –Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one-three-hundredths of a second, or 3.33 milliseconds
14. Smalldatetime –Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute
15. Char –Fixed-length non-Unicode character data with a maximum length of 8,000 characters
16. Varchar –Variable-length non-Unicode data with a maximum of 8,000 characters
17. Text –Variable-length non-Unicode data with a maximum length of 2^31 – 1 (2,147,483,647) characters
18. Binary –Fixed-length binary data with a maximum length of 8,000 bytes 184
19. Varbinary –Variable-length binary data with a maximum length of 8,000 bytes
20. Image – Variable-length binary data with a maximum length of 2^31 – 1 (2,147,483,647) bytes
Optional Column Constraints
The Optional Column Constraints are NULL, NOT NULL, UNIQUE, PRIMARY KEY and DEFAULT, used to initialize a value for a new record. The column constraint NULL indicates that null values are allowed, which means that a row can be created without a value for this column. The column constraint NOT NULL indicates that a value must be supplied when a new row is created. To illustrate, we will use the SQL statement CREATE TABLE EMPLOYEES to create the students table with 6 attributes or fields.
USE university
CREATE TABLE Students
(
StudentRegNo CHAR(10) NOT NULLUNIQUE,
StudentFNameCHAR(30)NOT NULL,
StudentMName CHAR(25)NOT NULL,
StudentLName CHAR(25)NOT NULL, Y
earEnrolledDATE NOT NULL,
BirthDate DATE NOT NULL,
CONSTRAINT students_PK PRIMARY
KEY(StudentRegNo
);
The first field is ‘StudentRegNo’ with a field type of CHAR. For this field, the field length is 10 characters, and the user cannot leave this field empty (NOT NULL).
Similarly, the second field is StudentFName with a field type CHAR of length 30. After all the table columns are defined, a table constraint, identified by the word CONSTRAINT, is used to create the primary key:
CONSTRAINT StudentsPK PRIMARY KEY(StudentRegNo)
We will discuss the constraint property further later in this unit. Likewise, we can create two more tables a ‘course’ table and ‘AcademicProgram’ table using the CREATE TABLE SQL DDL command as shown in the below example.
USE university
CREATE TABLE COURSE
(
CourseTitle Char(35) NOT NULL,
CourseCode Char(30) NOT NULL,
CourseUnitsInt NOT NULL,
CONSTRAINT COURSE_PK PRIMARY
KEY(CourseCode)
);
In this example, an ‘AcademicProgram’ table is created with three fields: ProgramID, ProgramName and CreditPoints.
USE university
CREATE TABLE AcademicPrograms
(
ProgramIDInt NOT NULL IDENTITY,
ProgramNameChar(50) NOT NULL,
CreditPointsInt NOT NULL,
CONSTRAINT AcademicPrograms_PK PRIMARY KEY(ProgramID)
);
INSERT INTO Statement
The INSERT INTO statement is used to insert new records in a table.
It is possible to write the INSERT INTO statement in two ways:
1. Specify both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:
INSERT INTO table_Name
VALUES (value 1, value 2 , value3 , …..);
Example
The following statements would create six records in the CUSTOMERS table.
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES ( 1 , “Ramesh” , 32 , “Ahmedabad” , 2000.00 );
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (2, “Khilan”, 25 , “Delhi” , 1500.00 );
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES ( 3 , “Kaushik” , 23, “Kota” , 2000.00 );
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES( 4, “Chaitali” , 25, “Mumbai” , 6500.00 );
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES( 5. “Hardik” , 27 , “Bhopal ” , 8500.00 );
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES( 6, “Komal , 22, “MP” , 4500.00 );
You can create a record in the CUSTOMERS table by using the second syntax as shown below.
INSERT INTO CUSTOMERS
VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );
All the above statements would produce the following records in the CUSTOMERS table as shown below.
ID | NAME | AGE | ADDRESS | SALARY |
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
2 | Khilan | 25 | Delhi | 1500.00 |
3 | Kaushik | 23 | Kota | 2000.00 |
4 | Chaitali | 25 | Mumbai | 6500.00 |
5 | Hardik | 27 | Bhopal | 8500.00 |
6 | Komal | 22 | MP | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
Relational Database schema with foreign key references
v Foreign key DBMS
A foreign key is different from a super key, candidate key or primary key because a foreign key is the one that is used to link two tables together or create connectivity between the two.
v What is a foreign key
A foreign key is the one that is used to link two tables together via the primary key. It means the columns of one table points to the primary key attribute of the other table. It further means that if any attribute is set as a primary key attribute will work in another table as a foreign key attribute. But one should know that a foreign key has nothing to do with the primary key.
v Use of foreign key
The use of a foreign key is simply to link the attributes of two tables together with the help of a primary key attribute. Thus, it is used for creating and maintaining the relationship between the two relations.
v Example of foreign key
Consider two tables Student and Department having their respective attributes as shown in the below table structure:
In the tables, one attribute, you can see, is common, that is Stud_Id, but it has different key constraints for both tables. In the Student table, the field Stud_Id is a primary key because it is uniquely identifying all other fields of the Student table. On the other hand, Stud_Id is a foreign key attribute for the Department table because it is acting as a primary key attribute for the Student table. It means that both the Student and Department table are linked with one another because of the Stud_Id attribute.
In the below-shown figure, you can view the following structure of the relationship between the two tables.
Relational Database schema with foreign key references
v Foreign key DBMS
A foreign key is different from a super key, candidate key or primary key because a foreign key is the one that is used to link two tables together or create connectivity between the two.
v What is a foreign key
A foreign key is the one that is used to link two tables together via the primary key. It means the columns of one table points to the primary key attribute of the other table. It further means that if any attribute is set as a primary key attribute will work in another table as a foreign key attribute. But one should know that a foreign key has nothing to do with the primary key.
v Use of foreign key
The use of a foreign key is simply to link the attributes of two tables together with the help of a primary key attribute. Thus, it is used for creating and maintaining the relationship between the two relations.
v Example of foreign key
Consider two tables Student and Department having their respective attributes as shown in the below table structure:
In the tables, one attribute, you can see, is common, that is Stud_Id, but it has different key constraints for both tables. In the Student table, the field Stud_Id is a primary key because it is uniquely identifying all other fields of the Student table. On the other hand, Stud_Id is a foreign key attribute for the Department table because it is acting as a primary key attribute for the Student table. It means that both the Student and Department table are linked with one another because of the Stud_Id attribute.
In the below-shown figure, you can view the following structure of the relationship between the two tables.
Screen shots of My SQL workbench (SQL commands and the results set)
Comments
Post a Comment