Database - Design & Implementation - 1

1. MS Alam TRIVUz Founder, TRIVUz Network DB01 DATABASE Design & Implementation TRIVUz Academy TRIVUz Academy Class Id:

2. Recap PF01 & PF02 TRIVUz Academy TRIVUz Academy

3. Recap We meet a customer We understand his needs We make a plan We scratch the design www.trivuzacademy.com Database Design TRIVUz Academy www.trivuzacademy.com

4. We are going to learn Brief introduction on Database Array Array Functions Global Array Loop for, do…while, for each Functions Database Design TRIVUz Academy www.trivuzacademy.com

5. Brief Introduction DATABASE TRIVUz Academy

6. Often abbreviated DB. A collection of information organized in such a way that a computer program can quickly select desired pieces of data . You can think of a database as an electronic filing system . TRIVUz Academy Brief Introduction DATABASE What is Database?

7. Traditional databases are organized by fields , records , and files . A field is a single piece of information; a record is one complete set of fields; and a file is a collection of records. Brief Introduction DATABASE What is Database? TRIVUz Academy

8. For example, a telephone book is analogous to a file. It contains a list of records, each of which consists of three fields: name, address, and telephone number. Brief Introduction DATABASE What is Database? TRIVUz Academy

9. For example, a telephone book is analogous to a file. It contains a list of records, each of which consists of three fields: name, address, and telephone number. Brief Introduction DATABASE What is Database? Name Address Phone Number Mr. Khan Banani , Dhaka 02-01020304 Farah Zebin Uttara , Dhaka 02-23344556 Robindranath Uttara , Dhaka 02-44543212 TRIVUz Academy

10. To access information from a database, you need a database management system (DBMS). This is a collection of programs that enables you to enter, organize, and select data in a database. Brief Introduction DATABASE DBMS TRIVUz Academy

11. A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model . Brief Introduction DATABASE RDBMS TRIVUz Academy

12. A short definition of an RDBMS is : A DBMS in which data is stored in tables and the relationships among the data are also stored in tables. The data can be accessed or reassembled in many different ways without having to change the table forms. Brief Introduction DATABASE RDBMS TRIVUz Academy

13. A short definition of an RDBMS is : A DBMS in which data is stored in tables and the relationships among the data are also stored in tables. The data can be accessed or reassembled in many different ways without having to change the table forms. Brief Introduction DATABASE RDBMS TRIVUz Academy

14. The database concept has evolved since the 1960s to ease increasing difficulties in designing, building, and maintaining complex information systems (typically with many concurrent end-users, and with a diverse large amount of data). It has evolved together with database management systems which enable the effective handling of databases. Brief Introduction DATABASE History TRIVUz Academy The database concept

15. The introduction of the term database coincided with the availability of direct-access storage (disks and drums) from the mid-1960s onwards. The term represented a contrast with the tape-based systems of the past, allowing shared interactive use rather than daily batch processing. Brief Introduction DATABASE History TRIVUz Academy Evolution of database and DBMS technology

16. In the earliest database systems, efficiency was perhaps the primary concern, but it was already recognized that there were other important objectives. One of the key aims was to make the data independent of the logic of application programs, so that the same data could be made available to different applications. Brief Introduction DATABASE History TRIVUz Academy Evolution of database and DBMS technology

17. The first generation of database systems were navigational , applications typically accessed data by following pointers from one record to another. The two main data models at this time were the hierarchical model, epitomized by IBM's IMS system, and the Codasyl model (Network model), implemented in a number of products such as IDMS. Brief Introduction DATABASE History TRIVUz Academy Evolution of database and DBMS technology

18. The Relational model , first proposed in 1970 by Edgar F. Codd, departed from this tradition by insisting that applications should search for data by content, rather than by following links. Brief Introduction DATABASE History TRIVUz Academy Evolution of database and DBMS technology

19. Relational systems placed heavy demands on processing resources, and it was not until the mid 1980s that computing hardware became powerful enough to allow them to be widely deployed. By the early 1990s, however, relational systems were dominant for all large-scale data processing applications, and they remain dominant today (2011) except in niche areas. Brief Introduction DATABASE History TRIVUz Academy Evolution of database and DBMS technology

20. The dominant database language is the standard SQL for the Relational model, which has influenced database languages also for other data models. Brief Introduction DATABASE History TRIVUz Academy Evolution of database and DBMS technology

21. SQL , or Structured Query Language , is a specialized type of programming language developed to work with relational databases such as MySQL, Oracle, Microsoft SQL Server, PostgreSQL , Informix, and others. The SQL standard is defined by ANSI, the American National Standards Institute in their ISO/IEC 9075:1992 document. (The standard is commonly referred to as ANSI SQL-92.) Every relational database applies its own version of the SQL standard; many enhance that standard. Standardizing the programming language allows the devel - oper to address the database in much the same way from platform to platform — and every major platform has such products written for it. Table 1-2 compares some popular relational-database products as illustrative examples. Quick Look DATABASE SQL ??? TRIVUz Academy What is SQL?

22. http :// math.hws.edu / vaughn / cpsc /343/2003/ history.html Brief Introduction DATABASE History TRIVUz Academy Ancient to modern

23. A DBMS has evolved into a complex software system and its development typically requires thousands of person-years of development effort. Some general-purpose DBMSs, like Oracle , Microsoft SQL server , and IBM DB2 , have been in on-going development and enhancement for thirty years or more. Brief Introduction DATABASE History TRIVUz Academy General- purpose DBMS

24. Three types of people are involved with a general-purpose DBMS: 1. DBMS developers - These are the people that design and build the DBMS product, and the only ones who touch its code. They are typically the employees of a DBMS vendor (e.g., Oracle, IBM, Microsoft, Sybase), or, in the case of Open source DBMSs (e.g., MySQL), volunteers or people supported by interested companies and organizations. They are typically skilled systems programmers. DBMS development is a complicated task, and some of the popular DBMSs have been under development and enhancement (also to follow progress in technology) for decades . Brief Introduction DATABASE History TRIVUz Academy Types of people involved

25. 2. Application developers and Database administrators - These are the people that design and build a database-based application that uses the DBMS. The latter group members design the needed database and maintain it. The first group members write the needed application programs which the application comprises. Both are well familiar with the DBMS product and use its user interfaces (as well as usually other tools) for their work. Sometimes the application itself is packaged and sold as a separate product, which may include the DBMS inside (see Embedded database; subject to proper DBMS licensing), or sold separately as an add-on to the DBMS . Brief Introduction DATABASE History TRIVUz Academy Types of people involved

26. 3. Application's end-users : (e.g ., accountants, insurance people, medical doctors, etc.) – These people know the application and its end-user interfaces, but need not know nor understand the underlying DBMS. Thus, though they are the intended and main beneficiaries of a DBMS, they are only indirectly involved with it. Brief Introduction DATABASE History TRIVUz Academy Types of people involved

27. The speed , accuracy , reporting and thoroughness of electronic databases make them critical to today’s 24/7 high-speed exchange of information. Brief Introduction DATABASE Advantages TRIVUz Academy

28. Speed: Format means quick storage and retrieval of information. Users and applications have a quick means for asynchronous reads and writes of data. Brief Introduction DATABASE Advantages TRIVUz Academy

29. Accuracy: Given careful data input, databases provide accurate and consistent results based on their data. Brief Introduction DATABASE Advantages TRIVUz Academy

30. Reporting: Information can be gathered, quantified, and custom-analyzed with greater flexibility. Brief Introduction DATABASE Advantages TRIVUz Academy

31. Thoroughness: Databases can store and report results as complete and detailed as their holdings — at electronic speed. Brief Introduction DATABASE Advantages TRIVUz Academy

32. Thoroughness: Databases can store and report results as complete and detailed as their holdings — at electronic speed. Brief Introduction DATABASE Advantages TRIVUz Academy

33. Choose RDBMS DATABASE Existing RDBMS TRIVUz Academy

34. Introducing MySQL DATABASE What is MySQL ? TRIVUz Academy MySQL is the worlds most popular open source RDBMS.

35. Introducing MySQL DATABASE Why MySQL ? TRIVUz Academy MySQL is the worlds most popular open source RDBMS. MySQL is used from many large scale web sites including Google and NASA, and is a very strong competitor to even the likes of Microsoft SQL Server and Oracle. MySQL is incredibly fast, provides support for full text searches, and is widely supported .

36. Introducing MySQL DATABASE Comparison of SQL Implementations TRIVUz Academy

37. Introducing MySQL DATABASE MySQL Enterprise-Level Database Features TRIVUz Academy

38. Introducing MySQL DATABASE MySQL Enterprise-Level Database Features TRIVUz Academy

39. Introducing MySQL DATABASE What MySQL Does Bes TRIVUz Academy Web applications: Web applications typically feature many reads and few writes. MySQL is fast and can meet the demands of Internet speed. In my experience, MySQL has proven time and again that it outperforms other RDBMS products in Web applications. Enterprise -level applications: MySQL offers support directly through the parent company, MySQL AB. MySQL’s feature set includes just about every- thing that an enterprise-level application would need. Refer back to Table 1-4 for more details. Open -source support: MySQL AB is responsive to requests for features as well. MySQL is open-source; everyone is welcome to download and extend the code to meet his or her needs .

40. Introducing MySQL DATABASE What MySQL Does Bes TRIVUz Academy Low overhead: MySQL runs comfortably for many applications on an Intel Pentium-class computer with 32 MB of RAM or less. I wouldn’t recommend running an enterprise-level MySQL implementation on such a system, but consider the utter futility of trying to run a Web application on Internet Information Server with Microsoft SQL Server that runs under Windows 2000 on a Pentium-class computer with 32 MB of RAM. Available large table size: MySQL tables can grow large, though they do sometimes encounter file-size limitations of the host operating system. Some architectures, however, can accommodate up to 8 terabytes (TB) per table using MySQL. Stability : All software is in development. Some features in MySQL are newer than others, making them possibly less stable than others. Table 1-6 shows some of the features within MySQL and their stability level.

41. Database Concepts & Design DATABASE What is data? TRIVUz Academy What is database? Why use a Database? How are database used?

42. Database Concepts & Design DATABASE Database Design TRIVUz Academy Database design is the process of determining and organizing the information to track. Many iterations of a database design occur as you try to achieve an efficient design and maximize your use of resources.

43. Database Concepts & Design DATABASE The Database Life Cycle TRIVUz Academy Requirements analysis Logical design of the database Physical design of the database Implementation and subsequent modification of the database

44. Database Design DATABASE Requirement Analysis TRIVUz Academy Gather information on the business process and items Analysis and Document them properly

45. Database Design DATABASE Logical Design TRIVUz Academy Entities and attributes At a basic level of a data model are entities — objects that you are interested in as part of the data model you are creating . Example – Marksheet Entities have identifying qualities — attributes. For example, the Marksheet entity has such attributes as StudentID , Subject and Marks.

46. Database Design DATABASE Logical Design TRIVUz Academy Entities Marksheet Attributes StudentID , Subject, Marks

47. Database Design DATABASE Example TRIVUz Academy Entities Marksheet Attributes Student ID Subject Marks 1001 Math 78 1003 Math 85 1005 Math 34 StudentID , Subject, Marks Table: marksheet

48. Database Design DATABASE TRIVUz Academy Okay, we designed our database. We got all our Entity & attributes. What now?

49. Database Design DATABASE TRIVUz Academy Next… We have to create our physical database.

50. SQL Introduction DATABASE TRIVUz Academy

51. SQL , or Structured Query Language , is a specialized type of programming language developed to work with relational databases such as MySQL, Oracle, Microsoft SQL Server, PostgreSQL , Informix, and others . SQL Introduction DATABASE What is SQL? TRIVUz Academy

52. The SQL standard is defined by ANSI, the American National Standards Institute in their ISO/IEC 9075:1992 document. (The standard is commonly referred to as ANSI SQL-92.) Every relational database applies its own version of the SQL standard; many enhance that standard. Standardizing the programming language allows the devel - oper to address the database in much the same way from platform to platform — and every major platform has such products written for it. Table 1-2 compares some popular relational-database products as illustrative examples. SQL Introduction DATABASE What is SQL? TRIVUz Academy

53. Syntax CREATE [TEMPORARY] TABLE [IF NOT EXISTS] < tablename > [(< create_statement >,...)] [ table_options ] [ select_statement ] SQL Example DATABASE Creating our Tables TRIVUz Academy

54. Code CREATE TABLE marksheet ( marks_id int (11) NOT NULL PRIMARY KEY, student_id int (11), subject varchar (255), marks varchar (255) ) ; SQL Example DATABASE Creating our Tables TRIVUz Academy

55. Thank You MS Alam TRIVUz Founder, TRIVUz Academy [email protected]