AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Sqlitestudio databases8/15/2023 ![]() Next, open the SQL code editor by clicking on Tools, then Open SQL Editor, or use the shortcut Alt + E. Create the table structures by running the PDM SQL code provided earlier in the chapter. Its name appears on the left-hand pane-double-click to open it. Next, name your future database file (e.g., filming_db ). Then click on the green button: The database creation window in SQLiteStudio Open SQLiteStudio and then create a database file by clicking on Database and then Add a database (or use the keyboard shortcut CTRL + O ). The most well-known software of this kind is SQLiteStudio, which you can download here. Therefore, you need some software to act as an interface between SQLite3 and you. But here, you want to talk to the SQLite3 RDBMS. If you haven't completed the previous step, download the CSVs.Ĭomputer programs use RDBMS. The method for exporting to CSV is described in the guidance notes. So, now you can export each worksheet into a CSV file. Import Your Data Into SQLite3Īll done! The worksheets in your spreadsheet will match your relational model. If you need more help, here are the worksheets for each of the five steps in the process. There should be enough information in the videos for you to do it yourself, but just in case, here are the formulas you can copy and paste into your spreadsheet. We’ll use the same method to create the production and production_company tables in the second video. In the screencasts, the file is opened in a text editor (e.g., Notepad, gedit, Atom, Notepad online, etc.). First, you’ll see how to sort your table by normalizing the content, deleting duplicates, and adding the primary and foreign keys. In this first video, you’ll learn how to create the location table. If you use Excel, you’ll need to create a function to normalize the text in your file before you start. If you use LibreOffice, follow the provided document showing you how to perform the steps described in the videos. You're now going to transform your Excel file to match the latest version of the relational model provided above.Įven though Excel and LibreOffice Calc are very similar, there are a few differences you need to know before watching the videos. Adapt Your CSV to Fit the Relational Model Here’s the SQL code for the PDM, which flows from the relational schema and is compatible with the SQLite3 RDBMS. So, here’s your adapted relational model after correcting the issues mentioned above and adding artificial keys: Latest version of the relational model However, don’t be surprised if you see the opposite happening in the work world, as good practices are rarely adopted. The PDM flows directly from the relational model (LDM).Īt the end of the chapter “Determine Your Primary Keys,” we discussed only using artificial keys in your LDM in specific cases, but you are encouraged to use them in all tables for the PDM. The PDM is highly dependent on the chosen RDBMS because each has its own variant of SQL and different features. Next, you need to develop the physical data model (PDM). To resolve the second point, remove the season attribute from the production table, meaning that you’ll lose this piece of information. It also means that the association between shooting_period and director will turn into a one-to-many relationship (instead of many-to-many). So, the name attribute in the director will no longer be atomic, which will violate the 1NF. To resolve the first point, allow director to hold a pair of directors in the same row. It’s a shame because it will impact the quality, but it will make it much simpler for you. Instead, we’re going to change the relational model. When this happens, you need to look at the filming date for each series, which would take too long. Either way, it's too tricky.įor series productions, the season number is often missing. You either need to check thousands of rows by hand and separate the pairs to resolve this or create a formula that will do it automatically. ![]() However, the director table can only have one director per row in your relational model. The Director column in your file is multivalued (i.e., some rows contain pairs of directors). Some data cleansing activities will be simple, while others are complex. So you first need to go through a process of data cleansing. You’ve created a high-quality relational model, so well done!īut the data you have available isn’t particularly high quality, and you're about to find out why. Adapt Your Relational Model and Create the PDM It would be mean (but true) to say at this point that all you've done is a couple of drawings your database doesn't exist yet! But thankfully, by the end of this chapter, you’ll have an actual database that you can query using SQL. You've worked hard to create your CDM and LDM.
0 Comments
Read More
Leave a Reply. |