smalldatetime date and time (1-minute precision) 12ġ3 Other types Data Types in SQL Server (3) timestamp automatically generated number whenever a change is made to the data row uniqueidentifier GUID identifier xml data in XML format 13ġ4 Data Types in SQL Server (4) Nullable and NOT NULL types All types in SQL Server may or may not allow NULL values Primary key columns Define the primary key Identity columns Automatically increased values when a new row is inserted (auto-increment values) Used in combination with primary key 14ġ5 1. Data Modeling in MySQL Workbench 10ġ1 Data Types in SQL Server Numeric bit (1-bit), integer (32-bit), bigint (64-bit) float, real, numeric(scale, precision) money for money (precise) operations Strings char(size) fixed size string varchar(size) variable size string nvarchar(size) Unicode variable size string text / ntext text data block (unlimited size) 11ġ2 Binary data Data Types in SQL Server (2) varbinary(size) a sequence of bits image a binary block up to 1 GB Date and time datetime date and time starting from to, a precision of 1/300 sec. Creating Databases in SQL Server Creating Tables Defining a Primary Key and Identity Columns Creating Relationships between the Tables One-to-many, Many-to-many, One-to-one 4. "Students are trained in courses" many-tomany relationship "Courses are held in towns" many-to-one (or many-to-many) relationship 9ġ0 1. When registering a new student the following information is entered: name, faculty number, photo and date. countries (BG, DE, US) and currencies (USD, EUR, BGN) 8ĩ Identification of Relationships Relationships are dependencies between the entities: We need to develop a system that stores information about students, which are trained in various courses.
Database modeling tools open source sql server how to#
Students have the following characteristics: Name, faculty number, photo, date of enlistment and a list of courses they visit 7Ĩ How to Choose a Primary Key? Always define an additional column for the primary key Don't use an existing column (for example SSN) Must be an integer number Must be declared as a primary key Use identity to implement auto-increment Put the primary key as a first column Exceptions Entities that have well known ID, e.g. Entities: Student, Course, Town 5Ħ Identification of Columns Columns in the tables are characteristics of the entities They have name and type For example students have: Name (text) Faculty number (number) Photo (binary block) Date of enlistment (date) 6ħ Identification of the Columns Columns are clarifications for the entities in the text of the specification, for example: We need to develop a system that stores information about students, which are trained in various courses. Filling test data in the tables 4ĥ Identification of Entities Entity tables represent objects from the real world Most often they are nouns in the specification For example: We need to develop a system that stores information about students, which are trained in various courses. Identification and modeling of relationships Multiplicity of relationships 5. Defining a primary key for each entity table 4. Identification of the columns in the tables 3. Data Modeling in MySQL Workbench 3Ĥ Steps in Database Design Steps in the database design process: 1.
0 kommentar(er)
